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

Minggu, 25 Agustus 2013

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

Hi John, no you are right I was fixing it... it was the start of the counter to stop the loop, but do not consider it pls.
The point is I can not copy the tables because it start to copy the same table (the table I call after the subCreateTableCopy).
BtW Thnaks

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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
>