Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

philips (Programmer) (OP)
23 Nov 00 11:33
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 (MIS)
29 Nov 00 15:54
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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close