Connection String to SQL Server in VBA

[Ivan Grozney]

I have a database that needs to connect to two databases on two different
servers. Security and the DBA doesn't want to make a linked server is the
reason for this mess.

The db servers are PCs with SQL Express and MSDE. They are not members of
our domain so I have local SQL accounts on each of them so I can connect.
Well, I can connect with a DSN but every time they close it down they need to
remember the passwords to reconnect. Ugh! So I would like to code the
connections into my VBA and when I distribute the MDE (or whatever it is in
A2K7) they cannot get to the password.

Immediately after the connections I run some queries that get data from both
systems to combine in some reports. But I cannot get the strings to work.

Here are two things that I have tried. This is my first try into this so I
am sure I am missing something (everything??) in getting this to work...
#1
Dim db2 As DAO.Database
Dim rst2 As DAO.Recordset
Dim strConnect2, strSQL3 As String

strConnect = "ODBC;Driver=SQL Server;Server=10.74.4.128; " & _
"Database=LIVEDATABASE;UID=FNLReader;PWD=r#adTSA"
strSQL3 = "SELECT * FROM [" & strConnect & "].Badge WHERE FALSE"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL3, dbOpenSnapshot)

#2
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strConnect, strSQL1 As String
strConnect = ODBC;DSN=ArtCA;SRVR=X.X.X.X;UID=UName;Pwd=PWD"
Set db = OpenDatabase("LBASE", dbDriverNoPrompt, False, strConnect)

strSQL1 = ""
strSQL1 = strSQL1 & "INSERT INTO dbo_..."
strSQL1 = strSQL1 & "WHERE BO.ZZZ Is Null;"

CurrentDb.Execute strSQL1, dbFailOnError + dbSeeChanges


TIA

Vanya

 

[Douglas J. Steele]

I believe you need braces around the provider name in the first case. You
might also try a semi-colon at the end. (I also thing the space you had
after the server name before the closing quote may be an issue.)

strConnect = "ODBC;Driver={SQL Server};Server=10.74.4.128;" & _
"Database=LIVEDATABASE;UID=FNLReader;PWD=r#adTSA;"

Not sure about the second one though.

Good sources of information about connections strings are
http://www.connectionstrings.com and
http://www.carlprothman.net/Default.aspx?tabid=81

 

[Sylvain Lafontaine]

The Devil hides in the details; so you must be sure to not mix up things.
For example, when you use a DSN, the server's address is already included in
the DSN so all you have to specify are the user name and the password.
Also, when you create a DSN (BTW, be sure to use System DSN and not a User
DSN), you have an option to save the password - along with the user name -
so you don't have to remember it. However, once the DSN has been created,
it's too late to change that so you'll have to delete and recreate it. Same
thing for ODBC Linked Tables: specify that you want the password to be save
when you create them or when you refresh them with the ODBC Linked Tables
Manager. Here's a two sites on connection strings:

http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81

As to your problem, the easiest way would be to use ODBC Linked tables.
When created, you can use these tables in the same way as if there were
local tables in your MDB or ACCDB database file.

Your first example probably doesn't work because you are using a syntax for
SQL-Server and not for JET. For JET, you must use the IN operator:

SELECT * FROM Badge IN [" & strConnect & "] WHERE FALSE

or:
SELECT * FROM Badge IN [" & strConnect & "] AS B WHERE 1=0

Your second example won't work because you use CurrentDB instead of DB and
you are also using dbo_ instead of dbo.

dbo_XXXX is for ODBC Linked Tables ***when*** the local name include dbo_ in
the name of the table(s).

Also, if you want heterogeous queries; ie., a query between two different
databases, then you must use ODBC Linked Tables or the first syntax, the one
with the operator IN; because create a database workspace will work against
only a single database. (Of course, you could create a linked server on the
SQL-Server but this is another topic and from the view of JET/Access, it
won't see this linked server as a different server but only as ordinary sql
views.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

I have a database that needs to connect to two databases on two different
servers. Security and the DBA doesn't want to make a linked server is the
reason for this mess.

The db servers are PCs with SQL Express and MSDE. They are not members of
our domain so I have local SQL accounts on each of them so I can connect.
Well, I can connect with a DSN but every time they close it down they need
to
remember the passwords to reconnect. Ugh! So I would like to code the
connections into my VBA and when I distribute the MDE (or whatever it is
in
A2K7) they cannot get to the password.

Immediately after the connections I run some queries that get data from
both
systems to combine in some reports. But I cannot get the strings to work.

Here are two things that I have tried. This is my first try into this so
I
am sure I am missing something (everything??) in getting this to work...
#1
Dim db2 As DAO.Database
Dim rst2 As DAO.Recordset
Dim strConnect2, strSQL3 As String

strConnect = "ODBC;Driver=SQL Server;Server=10.74.4.128; " & _
"Database=LIVEDATABASE;UID=FNLReader;PWD=r#adTSA"
strSQL3 = "SELECT * FROM [" & strConnect & "].Badge WHERE FALSE"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL3, dbOpenSnapshot)

#2
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strConnect, strSQL1 As String
strConnect = ODBC;DSN=ArtCA;SRVR=X.X.X.X;UID=UName;Pwd=PWD"
Set db = OpenDatabase("LBASE", dbDriverNoPrompt, False, strConnect)

strSQL1 = ""
strSQL1 = strSQL1 & "INSERT INTO dbo_..."
strSQL1 = strSQL1 & "WHERE BO.ZZZ Is Null;"

CurrentDb.Execute strSQL1, dbFailOnError + dbSeeChanges


TIA

Vanya

 

[Ivan Grozney]

Thank you both for your suggestions. I'll give them a try.

Vanya

 

[manikandanc]

how to connect the Dao vb5.0 with Sql server 2005.Send the Connection String,Please Help me out.

Advance Thanks!

 

[manikandanc]

The connection String for dao vb5 to sql connection2005,