How do I connect to remote server? 2

[Marty2of5]

This may be basic, but a vendor who holds some of our data on a dedicated server at their location just gave me update access to a SQL Server table.

From my Access 2000 environment, how do I access the data?
ADODB.Connection? What is the syntax to include the user name and password?

Thanks,
Marty

 

[kraxmo]

Dear Marty2of5,

You can connect to any SQL Server (ODBC or OLEDB) by using a DSN definition , UDL file, Registry value or hard-coded value(not recommended). DSN, UDL and the Registry allow you to specify a connection string external to your program. Check

http://msdn.microsoft.com

to learn how to create a DSN definitions, UDL files and Registry entries.

Here is an example of how to connect to a database using DSN, UDL and OLEDB data sources with appropriate connect string examples. (Note: code assumes correct IP/server/drivers/filenames/usernames exist with granted security.)

Function test()
    Const DSN_CONNECT_STRING = "DSN=Remote Vendor SQL Server"
    Const UDL_CONNECT_STRING = "File Name=C:\server.udl"
    Const OLEDB1_CONNECT_STRING = "Provider=SQLOLEDB;Data Source=255.255.255.255;Database=Northwind;uid=bill;pwd=gates;"
    Const OLEDB2_CONNECT_STRING = "Provider=SQLOLEDB;Data Source=[URL unfurl="true"]www.microsoft.com;Database=Northwind;uid=bill;pwd=gates;"[/URL]

    Dim cnSrc As ADODB.Connection
    Set cnSrc = New ADODB.Connection
    cnSrc.ConnectionString = OLEDB1_CONNECT_STRING
    cnSrc.Open
    cnSrc.Close
    Set cnSrc = Nothing
End Function

Just point the ConnectString above to your specified constant data source (DSN, UDL or connect string), and it should work.

To test it:
1. Set a breakpoint on the cnSrc.Open
2. Press Ctrl-G
3. Type ? test()
4. In the Immediate window, press Enter
5. Press F8 to execute the cnSlc.Open

If everything goes well, you should be able to see the current server and database (catalog) by doing the following in the Immediate window:
? cnSrc.Properties("Server Name&quot.Value
? cnSrc.Properties("Current Catalog&quot.Value Jim Kraxberger
Developing Access solutions since 1995

 

[Marty2of5]

That's what I needed!

Thanks,
Marty