ODBC vs ADODB.... ADODB.NET?

[kjv1611]

By the way, for using System DSNs, I found this bit of code from Microsoft, and it works well, it seems, but only for MS Access databases... so I'd need to modify it to fit other sources... I suppose all I need to do is dig in and find the driver information for each connection type, and then rebuild this for diff connection types, and reference it according to what connection I need to build.

What I need to check on if I go this route is I do not recall how to tell if a connection does not exist. Obviously I could do it through error handling, but what about checking manually when the database loads?

 

[kjv1611]

Here's the code - forgot to paste:

Option Compare Database
Option Explicit
Const ODBC_ADD_SYS_DSN = 4       'Add data source
Const ODBC_CONFIG_SYS_DSN = 5    'Configure (edit) data source
Const ODBC_REMOVE_SYS_DSN = 6    'Remove data source

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _
   hwndParent As Long, ByVal fRequest As Long, ByVal _
   lpszDriver As String, ByVal lpszAttributes As String) As Long
                    

Function Build_SystemDSN(DSN_NAME As String, Db_Path As String)

   Dim ret%, Driver$, Attributes$

   Driver = "Microsoft Access Driver (*.MDB)" & Chr(0)
   Attributes = "DSN=" & DSN_NAME & Chr(0)
   Attributes = Attributes & "Uid=Admin" & Chr(0) & "pwd=" & Chr(0)
   Attributes = Attributes & "DBQ=" & Db_Path & Chr(0)
   
   ret = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes)
   

   'ret is equal to 1 on success and 0 if there is an error
   If ret <> 1 Then
       MsgBox "DSN Creation Failed"
   End If

End Function

 

[jsteph]

Just my 2 cents...I've never run a passthru with anything but ODBC, and I don't see why (or how) one would use ado for that. In vb code, yes, ado is the way to go. But if you're just 'running' a passthru query--say as the source for a report or combo box for example--and this is not in code, then I'm not sure what one might gain.

I use a lot of passthrus for combo-box source, Report source, and they're all just "exec some_Procedure" perhaps with some parameters, and have never wanted or needed any thing that the odbc connection doesn't offer--at least for this context of passthru use.

Again, if you're talking about what to use in VBA code, that's a different story.
--Jim

 

[PHV]

find the driver information for each connection type
Have a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Thanks for the suggestion, PHV.. I'll see if I need that going forward... hopefully... though it'll probably be next week, way things are going today.. I was out of town when you posted last..