Getting information from Access

[vires]

Hi all,

wondering if anyone can help with a question:

I am working on a system that uses a excel sheet to cost a project that a company are working on. The excel sheet currently imports data from a csv file and sorts it all according to a PIPEKEY reference string i have defined as a unique identifier.

I have stored the associated pricing in a access database that also have matching PIPEKEY's assigned to them. I am at the point now of trying to associate the two together, so that when the excel sheets program is ran (data imported) the access prices will be imported also.

I have asked some friends at work about possible methods for this, 1 mentioned using MSQuery to match the pipekey's but as each key is different it seems i need a unique query for each string, and so upto 1500 queries.

Another passed on this possible coding, but aint sure what and how to get this functioning, can someone take a look and possibly comment on methods for such a process:

Sub situa()
Set DBmain = CreateObject("ADODB.Connection")
DBmain.Open "DSN=MS Access Database;DBQ=D:\putinhere\pathto\db1.mdb"

Set recset1 = CreateObject("ADODB.Recordset")

ctr = 1
Do Until ActiveCell.Value = ""
variable = Cells(3, 14).Value
sqltext = "select price from list where item=" & variable
recset1.Open sqltext, DBmain

Cell this = Price

recset1.Close: Set recset1 = Nothing
ctr = ctr + 1
Loop

End Sub

Would appreciate any help you guys could provide, im new to access and not a learner with VBA and excel programming, but willing to learn

Dean

 

[vires]

anyone got any ideas?

 

[bat17]

I am not sure if I am understanding you correctly so I will summarise a bit.
You have a table in Xl that has PIPEKEY to identify records
You have a table in Access using the same PIPEKEY values to add pricing information.

I have not tried to join tables from seperate sources in MSquery but I think that it should be doable. if PIPEKEY is a common field then you should be able to create a join between the two tables based on it and and join your records with just one query.

It may be easier/possible to import/link the CSV file in to Access and join them in Access and then pull the combined data to XL or just work in Access

If you use 'Colo's HTML Maker (bottom of page) to show some sample data we may get a better idea of how the tables relate to each other.

Also as you are workning from XL and just linking to an Access table it may be worth asking this in the XL forum ( its where most of the brains are anyway )

Peter

 

[vires]

thanks bat i will post over to the excel forums, left an image here to try explain it easier

 

[SydneyGeek]

One option would be to bring in the whole Access table with a query, and then use Excel to lookup the values. Unless you have huge numbers of records in the Access table, this should work OK, assuming the PIPEKEY is unique in Access.

Another option is to go the other way -- make the Excel table an external table in Access, and join up the data with an Access query.

Would either of these work for you?

Denis