dsn less connection

[spinkung]

Hi

i am trying to create a dsn less connection so i don;t have to go round to each pc to set up an odbc conn.

i have found this...

oConn.Open "Driver={SQL Server};" & _
           "Server=myServer;" & _
           "Database=myDB;" & _
           "Uid=myUser;" & _
           "Pwd=myPass"

but can't figure out what object type oConn should be. I've tried dao.connection, doesn't work. can anyone tell what the type of oConn should be or how i should be using the above.

Thanks.

 

[gemma-the-husky]

i suspect it is involved in the connect string of the tabledef object

tabledefs("sometable").connect .... describes the connection to the backend

for an access table it starts ;datadase=
for a ODBC connect it starts ;ODBC (from memory)
so it must be something similar for a DSN-less connection

 

[mdlueck]

Here is the code from my class which wraps the functionality you are describing:

I have another object it requires which reads the connection attributes out of the Windows Registry.

In the program initialization an instance of this class is created and retained the entire time the application is open.

The getter method, thus, always returns the same conneciton object the application is using.

Option Compare Database
Option Explicit

'Attributes
Private adoConn As ADODB.Connection

Private Sub Class_Initialize()
On Error GoTo Err_Class_Initialize

  Dim strDBConnectionString As String

  strDBConnectionString = "Driver=SQL Server;" & _
    "Server=" & ObjAppSettings.getdbServer & ";" & _
    "UID=" & ObjAppSettings.getdbUID & ";" & _
    "PWD=" & ObjAppSettings.getdbPWD & ";" & _
    "Database=" & ObjAppSettings.getdbDatabase

   Set adoConn = New ADODB.Connection
   adoConn.Open strDBConnectionString

Exit_Class_Initialize:
  Exit Sub

Err_Class_Initialize:
  Call errorhandler_MsgBox("Class: clsObjBEDBConnection, Function: Class_Initialize()")
  Resume Exit_Class_Initialize

End Sub

Function getADODBConnectionObj() As Variant
On Error GoTo Err_getADODBConnectionObj

  getADODBConnectionObj = adoConn

Exit_getADODBConnectionObj:
  Exit Function

Err_getADODBConnectionObj:
  Call errorhandler_MsgBox("Class: clsObjBEDBConnection, Function: getADODBConnectionObj()")
  Resume Exit_getADODBConnectionObj

End Function

 

[pbaldy]

so i don;t have to go round to each pc to set up an odbc conn.

As an alternative, you can create a File DSN on the server and use that to link tables; then nothing is required on each PC. Not saying it's better or worse than the DSN-less method, just saying it's out there.