INSERT data into local Access DB from remote Access DB

[bslintx]

I am trying to insert data from one Access database from another...I can get the following query to work on a remote Access database...

sSQL = "SELECT * FROM Activity IN """ & sRtdPath & """"

however, I need to include an inner join like so:

sSQL = "SELECT DISTINCT Machine.DisplayField, Machine.ValueField, Activity.IP FROM Activity INNER JOIN Machine ON Activity.ID_ = Machine.ActivityID WHERE Machine.DisplayField='OS Detected' AND Machine.ValueField<>'foo' AND Machine.ValueField NOT LIKE '%this%' AND Machine.ValueField<>'this';"

so ultimately, i need to insert data into a local Access table with data from another Access database table(s) inner join

is this possible? if not...suggestions?

Setup:1. 1 local Access database - contains vba module to loop through Access files (via FSO)2. multiple 'remote' (for the sake of simplicity - located in same directory as local) Access files - contains the data from which appends to local table

*** note - placed in multiple threads - will update if fix action found
thanks!

 

[genomon]

Is there a reason you cannot link tables from one to the other?

Gluais faicilleach le cupan làn

 

[bslintx]

ty genomon for responding...

i cannot link to the access files because they are dynamic - how many there are and the names change....hence using the file system object to find these files on-the-fly

i have it retrieving and inserting into an access database as so:

pseudo code -

For each file in directory
If access file
connect to file (ADO)

If not End of File
Do until end of file
Insert data into local database (VBA)
Loop
End If

End If
Next

...although this works just fine...I was hoping to not have to loop through the recordset...but instead use some sort of bulk insert...

i cannot seem to find via forums or google...i can only assume it's not easy or cannot be done

i suppose vba could create the links dynamically...however i do not want to create 50+ links and use a union to merge VERY large files

If I overlooked something...please let me know...again thanks!

 

[vbajock]

If the actual existence of the mdb is not known to the application until the moment you need to process it, your only way to accomplish what you want to do is thru the ADOX object. Once the loop you posted above finds the mdb, ADOX can be used to catalog what objects exist within the given database. The you can code your application to do what it needs to do with these objects by dynamically linking the tables you want to update and then executing your queries on these newly linked tables. You'll find a lot of code you can use for that job here:

http://www.oblique.ch/ms/ADOX_Faq.html