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