Query database for the names of all tables

[niftysam]

I am trying to make a combo box that will list all the table names in my db. I have no idea what I need to do. Thanks!!! I really need your help!!!

 

[Jon K]

Put a combo box on a form. Name the combo box as cboTableName

Put the following code in the On Open event of the form:

Private Sub Form_Open(Cancel As Integer)
    
    [b]Dim db As DAO.Database
    Dim tDef As DAO.TableDef
    Dim sTableName As String
    
    Set db = CurrentDb
    
    For Each tDef In db.TableDefs
       If Left(tDef.Name, 4) <> "MSys" Then
         sTableName = sTableName & tDef.Name & ";"
       End If
    Next
    
    Me.cboTableName.RowSourceType = "Value List"
    Me.cboTableName.RowSource = sTableName
    
    Set db = Nothing[/b]

End Sub

Note: The code is in DAO. If you use Access 2000 or higher, you must make a reference to the Microsoft 3.6 Object Library (when the code window is open, choose menu Tools, References... and select the library.)
.

 

[niftysam]

Thanks and now Im on to the next step!

Thanks..but now my second query is having issues. They can select the table in the combo box, but then I want to update this table with several pieces of information. How do I reference the combo box in my second query.

I know this is wrong..but i will post it

I reference the table selection on the form with
[Forms]![frmUpdateBranchInformation]![Combo2]


UPDATE [Forms]![frmUpdateBranchInformation]![Combo2], ZipCodesUSUnique INNER JOIN Branches ON ZipCodesUSUnique.AssignedBranch = Branches.Name SET ([Forms]![frmUpdateBranchInformation]![Combo2]).Branch = ZipCodesUSUnique.[AssignedBranch], ([Forms]![frmUpdateBranchInformation]![Combo2]).[Branch Distance] = ZipCodesUSUnique.Distance, ([Forms]![frmUpdateBranchInformation]![Combo2]).[Branch Address] = Branches.[Address], ([Forms]![frmUpdateBranchInformation]![Combo2]).[Branch City] = Branches.[City], ([Forms]![frmUpdateBranchInformation]![Combo2]).[Branch State] = Branches.[State], ([Forms]![frmUpdateBranchInformation]![Combo2]).[Branch Zip] = Branches.[ZipCode]
WHERE (([Forms]![frmUpdateBranchInformation]![Combo2]).[Zip]=(ZipCodesUSUnique.ZipCode));

 

[Jon K]

The correct syntax of an update statement is:-

UPDATE Table1 INNER JOIN Table2 ON [Table1].[Field1]=[Table2].[Field2] SET ........

You can't put two table names there in the position of Table1


Besides, if you know the table name, why do you need to reference the combo box on the form?

If you really need to reference the combo box on the form for the table name, you can build the update statement as an SQL string in VBA and run the SQL string in code.
.

 

[niftysam]

I don't know the name of the table until they select it in the combo box. Which is why I was referencing the combo box.

 

[Jon K]

The method of letting the user select the required table to update is useful only if the tables contain all the fields named in the update statement. Otherwise you will get an error.

If the tables are of the same structure, you can put the records in one table instead of using separate tables.
.

 

[Pat Hartman]

When your SQL is dynamic, you need to build it in a string and then execute the string.

strSQL = "UPDATE " & [Forms]![frmUpdateBranchInformation]![Combo2] & " As A INNER JOIN Branches ON ZipCodesUSUnique.AssignedBranch = Branches.Name SET (A.Branch = ZipCodesUSUnique.[AssignedBranch], A.[Branch Distance] = ZipCodesUSUnique.Distance, A.[Branch Address] = Branches.[Address], A.[Branch City] = Branches.[City], A.[Branch State] = Branches.[State], A.[Branch Zip] = Branches.[ZipCode]
WHERE A.[Zip] = ZipCodesUSUnique.ZipCode;"

I don't know that I edited the string correctly but the idea was to use an alias (As A) so that you could shorten the SQL string and not have to reference the form field containing the table name for every field in the query.

 

[Jon K]

strSQL = "UPDATE " & [Forms]![frmUpdateBranchInformation]![Combo2] & " As A INNER JOIN Branches ON ZipCodesUSUnique.AssignedBranch = Branches.Name SET .................... .................... ........................

Since it involves three tables: ZipCodesUSUnique and Branches plus the one selected by the user. the easiest way to get the correct systax is to first build the update query in query design, properly join the three tables and make sure the query works correctly. Then copy the SQL statement to VBA and replace the third table with the form reference, using an alias as shown by Pat.

To run the SQL string, you can use:-

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
.

 

[niftysam]

Thanks!!!!!

Thanks your everyone's help! Everything works great.

To answer jon's earlier questions: The tables do not have the same fields, instead I run two queries: One query adds 5 fields and then the second (the one everyone helped me on) populates the fields.

I do have 1 more questions. How could I check through VB script to see if the fields have already been created in the table. Currently, if I try run the first query on a table that already has the fields its gives me an error and I would rather is just move on to the next query.

again, I thank everyone for their help, and apologize for not being able to post the db.