assign destination name in an Access maketable query at runtime

[Guest]

How do I assign destination name in an Access maketable query at runtime.

 

[Allen Browne]

You can supply the target table name at runtime if you execute an append
query string instead of running a saved query.

1. Mock up a query that does what you need, using a dummy table name.

2. Switch it to SQL View (View menu, in query design.)

3. Copy the entire SQL statement to clipboard.

4. Paste it into VBA code, as a single line, or by closing quotes and adding
line continuation characters.

5. Execute the string.

You will end up with something like this:

Function RunMyAppendQuery(strTable As String)
Dim strSql As String
strSql = "INSERT INTO " & strTable & " ( Field1, Field2 ) " & _
"SELECT Surname, FirstName FROM tblClient;"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Thanks Allen just as I thought . .
The other half of my problem is reading this table in a query and extract
details - once again assign table name at runtime.

Your help is appreciated

 

[Allen Browne]

Same again.

Since you already have the name of the table in your code (strTable in the
previous example), you can OpenTable or OpenQuery or OpenRecordset
(depending on what you need to achieve) using that name in VBA code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message