DAO3.6 DSN-Less OpenConnection to SQL Server

[onedunpark]

Hello, Got a large DAO codebase to be updated, but for the time being my first task is to create a DSN-Less OpenConnection in DAO 3.6 to a SQL Server2000 database. Is this even possible? All of the examples I've come across relate to ADO. We'll get there eventually but are stuck with DAO in the short term. The followinfd code returns an 'Invalid Argument' message and I'm not sure what is exactly invalid.

Thanks in anticipation

Steven

Sub TESTCONN()

Dim vwkspc As Workspace
DefaultType = dbUseODBC

Dim vdbcon As Connection
' Initialise the program workspace
Set vwkspc = Workspaces(0)

Set vdbcon = vwkspc.OpenConnection("", _
dbDriverNoPrompt, _
Connect:="uid=USER1;pwd=PASS1;driver={SQL Server};" _
& "server=SQL1;database=DB1;&quot

 

[Nikita6003]

Hi

got this bit of code a while ago (i think from the databasejournal site) - it'll allow you to set up a DSN-less connection to a SQL Server db
using ODBC and DAO

Function LinkToPubsAuthorsDSNLess()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String

strConnect = "ODBC;DRIVER={SQL Server}" _
& ";SERVER=" & strServer _
& ";DATABASE=" & strDatabase _
& ";UID=" & strUID _
& ";PWD=" & strPWD & ";"

Set db = CurrentDb()
Set tdf = db.CreateTableDef("Authors")
tdf.SourceTableName = "Authors"

tdf.Connect = strConnect

db.TableDefs.Append tdf
db.TableDefs.Refresh

Set tdf = Nothing
Set db = Nothing
End Function

HTH

Cheers
Nikki