How to set up a DB connection to a remote DB not on the local machine

Hello, I am trying to adapt the following code to allow me to query one or more network databases. So far i have been testing the ADODB methods below on my local machine which is working 100%, but ultimately i would like to query a remote database sitting on the network at a known, static location.


I've tried altering the following statement to allow for remote connection without success (I pulled the entire routine shown at the bottom of this post from stackoverflow.com) -- this statement is where i declare the database name, username and password:

Please Login or Register  to view this content.

The ENVIRON("COMPUTERNAME") function returns my local machine name -- all computers i will be dealing with carry a SQL database with the same name (mydbname will be the same for all) and the sql "server" name for each machine is the computerName\mydbname.

I can connect to each remote DB in SQL directly - i have already added the ODBC connections for each on my computer -- and i've tried replacing the computer name part in the code above (environ("computername")) with the other machines computer name (which is the sql server name on that machine also) and I've tried putting in the IP address: Datasource=1.000.000.00\mydbname - doesn't work

Can anyone provide help on altering this code to allow for remote connections?

Please Login or Register  to view this content.

I've never done it like this before, as all my clients connect to the same SQL server database rather than individual ones what error message do you get?

Have you tried "\\" before the computer name?

It is the following error. Sorry, forgot to mention the // yes i did include it before the computer name.
THe error occurs as the line: objmyconn.open in the previous code.
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Run-time error '-2147467259 (80004005)':

Automation error
Unspecified error
---------------------------
OK Help
---------------------------

ok, i swear i entered this about 5 times... there must have been something wrong on the other end yesterday...the IP address worked this morning!

Please Login or Register  to view this content.

sweet.