RE: Need help with an If then statement before a loop

Yes it would. That is exactly what you would want to do. Create the query,
use it as a recordset then cycle throught that recordset checking the table
name. That would be the outer loop. Then when you find a table you want to
process, you do the inner loops. Below is untested air code, but it will
give the the basic idea.

Sub GFileCompile()
Dim db As Database
Dim recset1 As New ADODB.Recordset 'The input table
Dim recset2 As New ADODB.Recordset 'The product table (create ahead to
time)
Dim rstTableNames as NewADODB.Recordset 'Table Names to process
Dim specs As String
Dim intCount As Integer
Dim thefieldcnt As Integer
Dim strTable as String

rstTableNames.Open "SELECT [QueryName].TableName FROM [QueryName];", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

recset2.Open "SELECT [GFileCompile].*FROM [GFileCompile];",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'name of
destination table

Do While Not rstTableNames.EOF
strTable = rstTableName.TableName
If Instr(strTable, "G") > 0 Then
recset1.Open "SELECT [" & strTable.TableName & _
"].*FROM [604013G];", _
CurrentProject.Connection,
adOpenKeyset, adLockOptimistic 'name of the source table

thefieldcnt = recset1.Fields.count 'This is a count of the
fields in the
'table.

Do
**rest of loop here
count = count + 1
Loop Until count = thefieldcnt 'Stops after the last field
recset1.MoveNext
Loop Until recset1.EOF
recset1.Close
End If
Loop
recset2.Close
rstTableName.Close
End Sub



"MT_dma" wrote:

> I am able to get a list of table names through querying the MSysObjects table
> (Name, Type Criteria=1, Flags=0). If I did a make table query of the results
> would that work?
>
> "Klatuu" wrote:
>
> > Okay, that explains it. The problem is I don't know enough about ADODB to
> > know how to cycle through the table names in a database. My experience is
> > all with DAO, so the part where you can get a list of table names you will
> > have to figure out, but as far as the looping goes, you need the list of
> > table names to loop through and add a loop to your process.
> >
> > So, the outer loop would look at each table name, determine whether it has a
> > G, and if it does, then do your inner loops. Then, look at the next table
> > name, etc.
> >
> > Sorry I can't be more specific here, but my lack of ADO experience is a
> > problem.
> >
> > Best of Luck
> >
> > "MT_dma" wrote:
> >
> > > I guess you found one problem. I think the major problem is I am trying to
> > > cheat and use existing code. I should probably go back and re-think and re-do
> > > from the beginning. Ugh.
> > > I have inherited a database with 250 tables in it. Each table name has a
> > > alpha suffix denoting the type of data in the table. The G tables each
> > > contain one record. What I am trying to accomplish is appending records from
> > > all tables with a G suffix to a GCompile table, all the records with an E
> > > suffix to ECompile table, etc. It doesn't seem to me like it should be that
> > > difficult; however I can't figure out the words Access wants to hear.
> > > Just to be sure you are saying that I need to include the InStr in the first
> > > loop when I define the recordset?
> > >
> > > Thanks
> > >
> > > "Klatuu" wrote:
> > >
> > > > I am having trouble understanding what it is you are trying to do. You say
> > > > you want to select tables with a G in the name; however, you are opening
> > > > recset1 hard coded and outside both your loops. Can you be a little more
> > > > specific?
> > > >
> > > > recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,
> > > >
> > > > "MT_dma" wrote:
> > > >
> > > > > Here is my code. I copied it from one of my other db's and modified it. Right
> > > > > now it works when I input an existing table name. What I would like to do is
> > > > > have it loop through all the tables with a "G" in the table name and put
> > > > > those records in an existing table. I think the problem is in defining the
> > > > > recset. I can't figure out how to use the InStr function to define the recset.
> > > > > I'm still pretty new to coding and know that I'm not great at it so please
> > > > > be kind when you look at it :)
> > > > >
> > > > > Thanks.
> > > > >
> > > > > Sub GFileCompile()
> > > > > Dim db As Database
> > > > > Dim recset1 As New ADODB.Recordset 'The input table
> > > > > Dim recset2 As New ADODB.Recordset 'The product table (create ahead to
> > > > > time)
> > > > >
> > > > > Dim specs As String
> > > > > Dim count As Integer
> > > > > Dim thefieldcnt As Integer
> > > > >
> > > > >
> > > > > recset1.Open "SELECT [604013G].*FROM [604013G];", CurrentProject.Connection,
> > > > > adOpenKeyset, adLockOptimistic 'name of the source table
> > > > > recset2.Open "SELECT [GFileCompile].*FROM [GFileCompile];",
> > > > > CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'name of
> > > > > destination table
> > > > >
> > > > > thefieldcnt = recset1.Fields.count 'This is a count of the fields in the
> > > > > table.
> > > > >
> > > > > Do loop
> > > > > **rest of loop here
> > > > > End If
> > > > > count = count + 1
> > > > >
> > > > > Loop Until count = thefieldcnt 'Stops after the last field
> > > > > recset1.MoveNext
> > > > > Loop Until recset1.EOF
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > > "Klatuu" wrote:
> > > > >
> > > > > > Not enough info to gave a complete answer, but basically, you can use the
> > > > > > Instr() function to determine whether the table name has the character in it:
> > > > > >
> > > > > > If Instr(strTableName, "x") Then
> > > > > > 'Do the loop
> > > > > > End If
> > > > > >
> > > > > > strTableName is the name of the table and x is the character you want to
> > > > > > check for. If you need more detail, post your code.
> > > > > >
> > > > > > "MT_dma" wrote:
> > > > > >
> > > > > > > I have a loop in Access that is working, but what I would like to do is have
> > > > > > > the loop only work on tables that have a specific alpha character in the
> > > > > > > table name. I think the best way to accomplish this is to do an If - then
> > > > > > > statement before the loop, but I can't figure out the correct
> > > > > > > function/property to use. I tried using "Source" with wildcards and it didn't
> > > > > > > work. I'm a little out of my league on this one and would appreciate any help.
> > > > > > >
> > > > > > > Thanks.
.

Relevant Pages

  • Re: DAO MUCH faster than ADO in this test
    ... DAO is well-known to be faster than ADO. ... Of course the DAO loop ran faster than the SQL loop; ... advantage of a table-type recordset, which only works on local tables. ... Dim starttime As Single, finishtime As Single ...
    (microsoft.public.access.modulesdaovba)
  • Re: AutoTextList? UserForm? Macros? Not sure which way to go...
    ... Dim rs As DAO.Recordset ... ' Set the number of Columns = number of Fields in recordset ... the list of named ranges that exist in the Excel spreadsheet. ... The next change is to insert a For...Next loop around the code that reads ...
    (microsoft.public.word.vba.general)
  • Re: Befüllen aus RS klappt nicht
    ... Loop ... Dim rs1 As Recordset ... Wenn ich nun auch rs1.MoveNext aktiviere UND Next i belasse, ...
    (microsoft.public.de.access)
  • Re: Keep getting an Subscript Out of Range....
    ... sLabel As String ... Dim CircPts() As POINTAPI ... The recordset has a RecordCount property that is known ... before entering the loop. ...
    (microsoft.public.vb.general.discussion)
  • Re: access 2003
    ... Dim ctl As Control ... Dim rs As Recordset ... This sets the query definitions for choosing data to create an invoice using ... Event on combo box: Private Sub ChooseCust_AfterUpdate ...
    (microsoft.public.access.conversion)