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

Minggu, 25 Agustus 2013

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

Luigi-

I don't understand what you're doing with Do Until rs.EOF = rs.RecordCount.
EOF is a True/False, and RecordCount is an integer. I would have done:

Do Until rs.EOF

Maybe that will fix it.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of bondurri.luigi
Sent: Sunday, August 25, 2013 9:11 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [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

------------------------------------

Yahoo! Groups Links