access 97 linking tables and DSN

[philips]

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

 

[DougP]

See SQL Server uses the Servers name to find Installations of SQL. In our case we have 3 SQL servers that I can see.
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.