ACCESS FEED: [MS_AccessPros] Query loop with parameter values got from a table

Minggu, 25 Agustus 2013

[MS_AccessPros] Query loop with parameter values got from a table

Hi all,
I'm trying to create a procedure that copy ODBC table in local one with this schema:
1) I can get table names filtering a query (#Q1) from system TBL MSysObjects
2) I've to copy the table transforming them into local (I'm querying in #Q1 only the linked and ODBC tables
3) The main problem I've with the following code, is that I'm creating the tables, but they content the first table I used to call the procedure ! (all table have the right and different names got from #q1)

example
I've odbc tables called TBL_ODBC#1, TBL_ODBC#2, TBL_ODBC#3
I want to copy them in local TBL_ODBC#1_LOCAL, TBL_ODBC#2_LOCAL, TBL_ODBC#3_LOCAL
I get the table names from a query (selecting 4 and 6 table type) made on the system table MSysObjects

code I used:
_________________________________________________________
Dim rs As DAO.Recordset
Dim Name As String
Set rs = CurrentDb.OpenRecordset("SELECT [Name] FROM [#Q1]")

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst

Do Until rs.EOF = rs.RecordCount
strTempTable = rs!Name & "_LOCAL" ' Create name temptblOldTableName
DeletedTable:
DoCmd.CopyObject , strTempTable, Name, strTableName
rs.MoveNext
Loop

Else
MsgBox "There are no records in the recordset."
End If

MsgBox "looping finished"

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
Exit_subCreateTableCopy:
Exit Sub
Error_subCreateTableCopy:
MsgBox "Error in subCreateTableCopy " & Err.Number & " - " & Err.Description
Resume Exit_subCreateTableCopy
__________________________________________________________

What I use to call this code is

Call subCreateTableCopy("[table name]")

but I've to declare the [table name], so it works only on the table I decleared, but giving the right names to the multiple table...

some help please?
Have a great day!
Luigi