Using Access and Excel Together

Moved.

See the following: http://mrexcel.com/board2/viewtopic.php?t=86150

One way would be to add a second sheet and goto Data-->External Data-->New Database Query-->Microsoft Access. Follow the Wizard from there to select your Query and the fields that you want returned. Once set up, you can goto Data-->Refresh Data to update anytime the Access Table is updated.

Then in the sheet that you posted you can build VLOOKUPS based off of the Product ID #.

Although, this seems like a pretty round about way to do what you could do stand-alone in Excel (provided you don't have 65,536 products to list).

Why not create your Form in Excel and enter data that way?

Hope that helps,

Smitty

cause not every job we sell has the same product...

What im doing is making a small book with all the product on that particular job.

We sell lighting to new construction apartment complexes. We have thousands of items.

My problem is I have too many books to do in a day and cant keep up ... Business is way good right now and I cant keep up. So I want to make it faster .. every minute saved is good.

Lets say we have 1000 items available... And this job want 20 of them... Ide like to be able to just enter the number in the appropriate cell and all the other information about the item in taken from the database and entered.. Including the image.

Hello Richard,

RichardofHS said:

Lets say we have 1000 items available... And this job want 20 of them... Ide like to be able to just enter the number in the appropriate cell and all the other information about the item in taken from the database and entered.. Including the image.

Click to expand...

You can't query images via ODBC or OLEDB that I'm aware of. Here's some OLE automation, how you will make it contingent is up to you.

<font face=Courier New><SPAN style="color:darkblue">Private</SPAN> <SPAN style="color:darkblue">Declare</SPAN> <SPAN style="color:darkblue">Function</SPAN> DeleteFile <SPAN style="color:darkblue">Lib</SPAN> "kernel32" Alias "DeleteFileA" _
    (<SPAN style="color:darkblue">ByVal</SPAN> lpFileName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>) <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>

<SPAN style="color:darkblue">Sub</SPAN> test()
<SPAN style="color:green">' Example grabs Image1 from Form: MyLogo</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> fNum <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> bArray() <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Byte</SPAN>, cArray() <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Byte</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> lngRet <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> myPic <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> objAccess <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Const</SPAN> MyLogo <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN> = "C:\temp\MyLogo.Emf"

<SPAN style="color:darkblue">Set</SPAN> objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "c:\temp\myDb.mdb"

objAccess.DoCmd.SelectObject ObjectType:=2, _
        ObjectName:="MyLogo", InDatabaseWindow:=<SPAN style="color:darkblue">True</SPAN>

objAccess.DoCmd.OpenForm "MyLogo", 0, , , 1
<SPAN style="color:darkblue">ReDim</SPAN> bArray(LenB(objAccess.Forms!MyLogo.Image1.PictureData) - 1)
<SPAN style="color:darkblue">ReDim</SPAN> cArray(LenB(objAccess.Forms!MyLogo.Image1.PictureData) - (1 + 8))
bArray = objAccess.Forms!MyLogo.Image1.PictureData
objAccess.DoCmd.<SPAN style="color:darkblue">Close</SPAN> 2, "MyLogo", 2

objAccess.CloseCurrentDatabase
objAccess.Quit
<SPAN style="color:darkblue">Set</SPAN> objAccess = <SPAN style="color:darkblue">Nothing</SPAN>

<SPAN style="color:darkblue">For</SPAN> lngRet = 8 <SPAN style="color:darkblue">To</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(cArray)
    cArray(lngRet - 8) = bArray(lngRet)
<SPAN style="color:darkblue">Next</SPAN>
fNum = FreeFile
<SPAN style="color:darkblue">Open</SPAN> MyLogo <SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Binary</SPAN> <SPAN style="color:darkblue">As</SPAN> fNum
Put fNum, , cArray
<SPAN style="color:darkblue">Close</SPAN> fNum

Application.ScreenUpdating = <SPAN style="color:darkblue">False</SPAN>
<SPAN style="color:darkblue">Set</SPAN> myPic = Sheets(1).Pictures.Insert(MyLogo)
<SPAN style="color:darkblue">With</SPAN> myPic
    .Top = Sheets(1).[g4].Top
    .ShapeRange.Height = Sheets(1).[g4].RowHeight * 6.2
    .Left = Sheets(1).[g4].Left - .Width
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">Set</SPAN> myPic = <SPAN style="color:darkblue">Nothing</SPAN>
Application.ScreenUpdating = <SPAN style="color:darkblue">True</SPAN>

<SPAN style="color:darkblue">Call</SPAN> DeleteFile(MyLogo)

<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Use MS Query for the data. Ta da.