How To Open a Linked Microsoft SQL Table
This code will allow you to open and read a linked Microsoft SQL table.
Program Code
Option Compare Database
Option Explicit
' ***********************************************************************
' List Linked Table Names In Database With Record Counts
' ***********************************************************************
Public Function CreateLinkedTablesWithRecords()
Dim db As DAO.Database
Dim recIn As DAO.Recordset
Dim recOut As DAO.Recordset
Dim tbl As TableDef, fld As Field
Dim lngRecordCount As Long
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteTableNames", acViewNormal, acEdit
DoCmd.SetWarnings True
' ***********************************************************************
' Open the Output File
' ***********************************************************************
Set db = CurrentDb()
Set recOut = db.OpenRecordset("tblTableNames")
' ***********************************************************************
' Loop Through and Create The Min Max Factor Table
' ***********************************************************************
For Each tbl In db.TableDefs
If Left$(tbl.Name, 4) <> "MSys" And Left$(tbl.Name, 1) <> "~" Then
On Error Resume Next
Set recIn = db.OpenRecordset(tbl.Name, dbOpenDynaset, dbSeeChanges)
If Err.Number <> 0 Then
Debug.Print tbl.Name
Err.Clear
GoTo ContinueTableScan
End If
If Not recIn.EOF Then
recOut.AddNew
recOut!TableName = tbl.Name
recOut.Update
recIn.Close
Set recIn = Nothing
End If
End If
ContinueTableScan:
Next tbl
recOut.Close
Set recOut = Nothing
Set db = Nothing
End Function