re: Accessing external data using the IN clause

Tony Toews - Access MVP — Sat, 28 Mar 2009 07:48:10 GMT

This is a useful technique and there is very little useful examples on this topic in the help files. So it's very much a matter of trial and error figuring this out.

I use this technique while updating backend MDB files to the latest version.

When the user selects the BE MDB, or upon startup, the FE MDB first update the backends tables, fields, indexes and relationships to the latest version using DAO collections.

Then if there is any table clean up to be done or records to be inserted in a status table of some sort I then use the IN clause in an Action query.

For example to create a "master" table of service techs from a text field entered on each individal service order in previous versions of my app I used the following:

' Append records to ServiceRecords table

strSQL = "INSERT INTO ServiceTech ( stServiceTech ) " & _

" IN '" & strDatabasePathandName & "' " & _

"SELECT srServicePerson FROM ServiceRecords " & _

" IN '" & strDatabasePathandName & "' " & _

"GROUP BY srServicePerson " & _

"HAVING (srServicePerson Is Not Null);"

CurrentDb.Execute strSQL, dbFailOnError

Note that the strDatabasePathandName is just a share, path and file name or drive letter, path and file name. No need to use [;DATABASE=...] etc.

I then update the backend version number in a one field, one record table. For example:

strSQL = "SELECT zDataVersionNumber FROM zDataVersionNumberApp IN '" & strDatabasePathandName & "';"

is passed to an OpenRecordset statement with some further logic. That could've been an Action query too.

re: Accessing external data using the IN clause

Kevin — Tue, 31 Mar 2009 22:06:43 GMT

Very interesting! I can see some uses here. I'm building a data sync operation in an app that collects from multiple work PCs and creates a single coordinating database, and the environment does not warrant (allow) a client-server backend.

re: Accessing external data using the IN clause

Renaud Bompuis — Mon, 06 Apr 2009 05:25:04 GMT

Nice article.

Access' ability to easily link to external datasources and manipulate them as if they were its own is really one of its greatest strengths.