Hello Everyone!
I have very specific reasons for this particular request.
I want to open the Link ODBC tables dialog box and be viewing the remote data tables list so that I can select the the needed table.
I want to do this programmatically.
In Access 2010 This is done manually by:
Clicking the "External Data" Tab on the ribbon
Then Clicking "ODBC Datebase"
Then Clicking "Link to the data source by creating a linked table"
Then by selecting the desired DSN file
Then entering the password
Then next thing that opens is the "Link Tables" window. <<<<<<<<<<<< This is where I need to be VIA code.
This is where I need to be programmatically via VBA with MS Access
I already know how to link via code. In my case, in this request, that is Not what I want to do.
Very specifically, I am required to open the "Link Tables" window with VBA code without using sendkeys or pre-stored macros.
The image below shows where I am required to be when a single button on our form is clicked.
If anyone knows how to do this please let me know asap.
Thank you
AFTER EXTENSIVE SEARCHING...
I settled
Dim stConnect As String
stConnect = "PROVIDER=SQLOLEDB.1;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=" & SQLServerDataBaseName & ";DATA SOURCE=" & SQLServerConnection & ",1433;USER ID=" & SQLServerUN & ";PASSWORD=" & SQLServerPW
'OR USE the difference is the SQLServerIP versus SQLServerConnection
'IMPORTANT netsol needs the ",1433" after the SQLServerIP or SQLServerConnection
However some other servers do not require it."000.000.000.000,1433" replacing 000 with the actual ip, of course
'stConnect = "PROVIDER=SQLOLEDB.1;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=" & SQLServerDataBaseName & ";DATA SOURCE=" & SQLServerIP & ",1433;USER ID=" & SQLServerUN & ";PASSWORD=" & SQLServerPW
Dim DBOdbWT As ADODB.Connection
Dim DBOrsWT As ADODB.Recordset
Set DBOdbWT = New ADODB.Connection
DBOdbWT.Open stConnect
Set DBOrsWT = DBOdbWT.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
With DBOrsWT
Do While Not DBOrsWT.EOF
TablesListVar = TablesListVar & !TABLE_NAME & vbCrLf
DBOrsWT.MoveNext
Loop
DBOrsWT.Close
End With
DBOdbWT.Close
MsgBox "The Following is a list of tables in the Remote ODBC Database """ & SQLServerDataBaseName & """" & vbCrLf & vbCrLf & TablesListVar, , "RemoteODBC Tables List"
This should produce a list of remote tables in the db from here you can do what is needed with the list.