ODBC

[Gary.Lowe]

Dear All

Does anybody know if you can create an ODBC datsource through code.

Thanks

[Dr_Evil]

Yes, it is possible. I pulled this code snippet from MSDN. Take a look.

'Connection Object, Connections Collection Example

'This example demonstrates the Connection object and
'Connections collection by opening a Microsoft Jet Database
'object and two ODBCDirect Connection objects and listing 
'the properties available to each object.

Sub ConnectionObjectX()

   Dim wrkJet as Workspace
   Dim dbsNorthwind As Database
   Dim wrkODBC As Workspace
   Dim conPubs As Connection
   Dim conPubs2 As Connection
   Dim conLoop As Connection
   Dim prpLoop As Property

   ' Open Microsoft Jet Database object.
   Set wrkJet = CreateWorkspace("NewJetWorkspace", _
      "admin", "", dbUseJet)
   Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")

   ' Create ODBCDirect Workspace object and open Connection
   ' objects.
   Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
      "admin", "", dbUseODBC)
   Set conPubs = wrkODBC.OpenConnection("Connection1", , , _
      "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")
   Set conPubs2 = wrkODBC.OpenConnection("Connection2", , _
      True, "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

   Debug.Print "Database properties:"

   With dbsNorthwind
      ' Enumerate Properties collection of Database object.
      For Each prpLoop In .Properties
         On Error Resume Next
         Debug.Print "  " & prpLoop.Name & " = " & _
            prpLoop.Value
         On Error GoTo 0
      Next prpLoop
   End With

   ' Enumerate the Connections collection.
   For Each conLoop In wrkODBC.Connections
      Debug.Print "Connection properties for " & _
         conLoop.Name & ":"

      With conLoop
         ' Print property values by explicitly calling each
         ' Property object; the Connection object does not
         ' support a Properties collection.
         Debug.Print "  Connect = " & .Connect
         ' Property actually returns a Database object.
         Debug.Print "  Database[.Name] = " & _
            .Database.Name
         Debug.Print "  Name = " & .Name
         Debug.Print "  QueryTimeout = " & .QueryTimeout
         Debug.Print "  RecordsAffected = " & _
            .RecordsAffected
         Debug.Print "  StillExecuting = " & _
            .StillExecuting
         Debug.Print "  Transactions = " & .Transactions
         Debug.Print "  Updatable = " & .Updatable
      End With

   Next conLoop

   dbsNorthwind.Close
   conPubs.Close
   conPubs2.Close
   wrkJet.Close
   wrkODBC.Close

End Sub

[Gary.Lowe]

Thanks Dr E

But isn't that opening ODBC Datasources, I wanted to know if you can create one.

I want to be able to create an ODBC user datasource for a SQL Server database.

[barrk]

These are just some examples. I don't know what you are using as your front end. Hope this helps!


HOWTO: Create ODBC Data Sources Using SqlConfigDataSource


Example 1:
* Code to create a new datasource for an Excel file.
*
* First you need to use the Declare DLL function to prototype the
* SQLConfigDataSource function
***
DECLARE Integer SQLConfigDataSource in odbccp32.dll Integer, Integer,;
String, String
***
* Now you need to create a string containing the settings appropriate
* to the driver you are using.. the following is an example for a tier 1
* Microsoft Excel ODBC driver accessing the Schedule.xls file.
***
settings="DSN=NewExcelDataSource"+chr(0)+;
"Description=NewExcel Description"+chr(0)+;
"FileType=Excel 5.0"+chr(0)+;
"DBQ=C:\schedule.xls"+chr(0)+;
"MaxScanRows=16"
* Note: If you have spaces on either side of the equal sign (=), this
* code will not work.
? SQLConfigDataSource(0,1,"Microsoft Excel Driver (*.xls)",settings)
***
Example 2:
* Code to create a new datasource to a VFP table.
*
* First you need to use the Declare DLL function to prototype the
* SQLConfigDataSource function
***
DECLARE Integer SQLConfigDataSource in odbccp32.dll Integer, ;
Integer, String, String
***
* Now you need to create a string containing the settings appropriate
* to the driver you are using. The following is an example for the
* Microsoft VFP ODBC driver accessing the customer.dbf file.
***
settings="DSN=VFP Tables"+chr(0)+;
"Description=VFP ODBC Driver"+chr(0)+;
"SourceDB=c:\vfp\samples\data\customer.dbf"+chr(0)+;
"SourceType=DBF"

* Note: If you have spaces on either side of the equal sign (=), this
* code will not work.
? SQLConfigDataSource(0,1,"Microsoft Visual FoxPro Driver",settings)

***
* Now you can create a view to these datasources either through the view
* designer or in code.
***

[barrk]

set objconn = createObject("ADODB.Connection")
objconn.open "driver={sql server};server=SQLSERVER;database=Logging;uid=administrator;Pwd=beer"
set mycommand = CreateObject("ADODB.Command")
Set mycommand.ActiveConnection = objconn
Set myrs = CreateObject("ADODB.Recordset")
mycommand.commandtext = "Select * from sometable"
myrs.open mycommand