access 97 linking tables and DSN
access 97 linking tables and DSN
(OP)
I have two SQL server databases running on two machines
and I want to link an
Access 97 Table so that a table refers to either
one of the SQL servers depending on the setting of a DSN
on the PC running the Access database.
When I link the table and look at the connect string
it has the sql server machine name contained with it.
All I want to specify in the connect string is the
username,database, password - NOT the machine.
Is the machine name in the connect string taken into account?
Thanks
and I want to link an
Access 97 Table so that a table refers to either
one of the SQL servers depending on the setting of a DSN
on the PC running the Access database.
When I link the table and look at the connect string
it has the sql server machine name contained with it.
All I want to specify in the connect string is the
username,database, password - NOT the machine.
Is the machine name in the connect string taken into account?
Thanks
Talk To Other Members
RE: access 97 linking tables and DSN
Anythime I go to a client and want to connect it shows the Servers Network name.
See even in this DSN less connection example you have to provide the Servers name:
------------------------------------------
Dim Conn As ADODB.Connection
Dim Rs1 As ADODB.Recordset
Set Conn = New ADODB.Connection
Set Rs1 = New ADODB.Recordset
Conn.Open "driver=SQL Server;server=smallbserver;uid=sa;pwd=;database=Universal;"
Dim SQLCode As String
SQLCode = "Select Max(PURCH_ORDER) AS LastPO From PO"
Rs1.Open SQLCode, Conn, adOpenStatic, adLockOptimistic
'Do some stuff with the recordset
Rs1.Close
Conn.Close
Set Rs1 = Nothing
Set Conn = Nothing
-------------------------------
So you would have to write code somehow to see which server to use.
Like so:
Dim Conn As ADODB.Connection
Dim Rs1 As ADODB.Recordset
Set Conn = New ADODB.Connection
Set Rs1 = New ADODB.Recordset
If "something" then
'connect to this server SQL
Conn.Open "driver=SQL Server;server=smallbserver;uid=sa;pwd=;database=Universal;"
Else
'connect to this other server SQL
Conn.Open "driver=SQL Server;server=accserver;uid=sa;pwd=;database=Accounts;"
End if
Dim SQLCode As String
SQLCode = "Select Max(PURCH_ORDER) AS LastPO From PO"
Rs1.Open SQLCode, Conn, adOpenStatic, adLockOptimistic
'Do some stuff with the recordset
Rs1.Close
Conn.Close
Set Rs1 = Nothing
Set Conn = Nothing
---------------------------
OK
DougP, MCP
dposton@universal1.com
Ask me how Bar-codes can help you be more productive.