INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!

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

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

RE: access 97 linking tables and DSN

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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!

Resources

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