This does not compile but it shows what I am after. I am essentially wanting to iterate tables on a SQL Server database to DSNLess link into an acacess database and pass in the following parameters
Code:
stLocalTableName
stRemoteTableName
pkfield
stServer
stDatabase
stUsername
stPassword
This is the syntax, I was wanting to use (I know this does not compile so it will require some work) but illustrates the point
Code:
Public Sub GetTableNames()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim f As ADODB.Field
Dim conn As String
Set c = New ADODB.Connection
With c
.Provider = "sqloledb.1"
With .Properties
.Item("Data Source") = "Server"
.Item("Initial Catalog") = "Database"
.Item("PassWord") = "password"
.Item("User ID") = "user"
End With
.Open
Set r = .OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
With r
While Not .EOF
If (.Fields("TABLE_TYPE") = "TABLE" And .Fields("TABLE_NAME") Like "Test*") Then
Debug.Print .Fields("TABLE_NAME")
Debug.Print Mid(.Fields("TABLE_NAME"), InStr(1, .Fields("TABLE_NAME"), "_") + 1)
AttachDSNLessTable (Mid(.Fields("TABLE_NAME"), InStr(1, .Fields("TABLE_NAME"), "_") + 1), .Fields("Table_Name"), .Item("Data Source").Value, .Item.("Initial Catalog").Value, .Item("User ID").Value, .Item("PassWord").Value))
End If
.MoveNext
Wend
End With
End With
End Sub