DAO Connection to SQL Server

[yu217171]

Hi everyone,

Does anyone know how to connect to SQL Server using DAO?

Here's some code I dug up but it doesn't work.

Dim connString As String
connString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=MyServerName"

Dim wrkSpace As Workspace
Set wrkSpace = DBEngine.CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)

Dim dbsPic As Database

Set dbsPic = wrkSpace.OpenDatabase("", dbDriverComplete, False, connString)

 

[hmckillop]

In the above code did you create a DSN to connect to the database?

DAO code assumes database is local and while it can deal with ODBC data (Oracle, DB2, SQL Server, Paradox, FoxPro) it has no special facilities for dealing with the data

The syntax is something like:

Set dbtemp = wstemp.OpenDatabase _
    ("", dbrivernoprompt, False, "DSN= ;UID= ;PWD= ;")

filling in the blanks for the DSN (Data Source name), UID and PWD (password) with the DSN having been previously created on the machine - i think you need a machine DSN or file DSN.

Also I think it depends on the version of SQL you are using whether you will be able to do this,
because the ODBC driver that ships with SQL Server 2000 exposes new GUID and Unicode data types when connecting to SQL Server, DAO version 3.x does not work properly with SQL Server 2000. So ensure you have a later version of DAO

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[bjd4jc]

I hate to ask, but why do you need to use DAO to do this? Why not use ADO?

 

[yu217171]

Thanks hmckillop. I created an ODBC System DSN and it worked.

The reason why I'm using DAO is because we have a legacy app designed in VB and rehauling to use ADO or .NET is just not worth it.

Thanks again everyone.

Keith