How do I loop through an Access table

[sibleytr]

I have an Access MDB (FE) that connects to multiple Access MDB (BE) data tables. The BE MDBs all have the same number of tables and names. In order to link to the different BE MDBs I have a comboBox that list the various BE MDB names and their locations (this table is stored in the FE MDB) and this part of the code is working. However, in order to link to the tables inside the BE MDBs I've had to hard code the table names.

As you can expect, I would like to do away with my hardcoding and automate my link connection process. I've created a table (also stored in the FE MDB) that contains all the table names available in my BE MDBs. The goal is to loop through the table containing all the table names and thus do away with the hardcoding need. However my coding appears to be lacking.

I keep receiving a Compile error: User-defined type not defined message at the Dim db As Database.

    Dim strLinkType As String  'Production, Development, etc
    Dim strLinkPath As String  'Location of BE
    Dim strDataFile As String  'Name of BE MDB

    Dim strCombo As String  'Combo Box Value
    strCombo = comboLinkSelection.Value
    
    Select Case strCombo
        Case "<Select Link>"  
            MsgBox "Please select your" & vbCrLf & " desired link type" & vbCrLf & "  from the drop" & vbCrLf & "    down list.", vbOKOnly, "Warning"
        End
        Case strLinkType
            Dim tblName As String
            Dim db As Database  ' *** Error ****
            Dim rs As Recordset
            
            Set db = db1
            Set rs = db.OpenRecordSet                  (Name:="tblInfoLinkTables")
            rs.MoveFirst
            
            Do Until rs.EOF
                tblName = rs.Fields(0).Value
                On Error Resume Next
                    DoCmd.DeleteObject acTable, tblName
                If Err.Number <> 0 Then
                    Err.Clear
                End If
                    DoCmd.TransferDatabase _
                    acLink, "microsoft Access", _
                    strLinkPath & "/" & strDataFile, _
                    acTable, tblName, tblName
                rs.MoveNext
            Loop
            'Reset comboBox Value
            Me.comboLinkSelection.Value = "<Select Link>"
            'Advise the link process is complete
            MsgBox "Your MDB has been set for " & vbCrLf & "         " & strLinkType, vbOKOnly, "Data Link Notice"
        End
    End Select

Any ideas would be helpful.

 

[PHV]

You have to reference the Micrososoft DAO 3.x Object Library:
when in VBE menu Tools -> References ...

In your code, replace this:
Dim rs As Recordset
By this:
Dim rs As DAO.Recordset

A link of interrest for you:

http://www.peterssoftware.com/tlk.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

TankHumper,
Sounds suspiciously like a references error. From a code window, select Tools->References from the menu. Ensure "Microsoft DAO 3.X Object Library" is selected. Then either move the DAO reference higher in the list until it's above the ActiveX Data Objects library or in your variable declaration, specify DAO:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Recompile and you should be all set.

Ken S.

 

[sibleytr]

I enabled the MS DAO reference and reset the Dim as DAO.<> and all is well.

Thanks for the info.