Create ODBC connection through code 1

[MICKI0220]

I have an Access 2002 database that I have linked to an SQL 2008 database. This is a new link and instead of going to 150 user pc's and creating an ODBC connection manually, I would like to create the connection in the Main form of my Access Database. Is this possible? The only thing I can find is the connection string and I get errors of incorrect syntax. Below is the code I placed in the Load area of my main form.....


Driver={SQL Server Native Client 10.0};Server=CTIDATA;Database=Jobs;Trusted_Connection=yes;



I have also tried:


Dim conn As AODBC.Connection
Dim rst As ADODB.Recordset
Dim sql As String
conn.Open "Driver={SQL Server};" & _
"Server=CTIDATA;" & _
"Database=Jobs;" & _
"UID=;" & _
"PWD=;"

I get a "compile error" user define type not defined..


Any help would be appreciated.

 

[PHV]

user define type not defined
Add a refererence to the Microsoft ActiveX Data Object Library.

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

 

[MICKI0220]

Can you explain that for me. I've never done that.

 

[SkipVought]

Tools > References... in the VB Editor.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MICKI0220]

There were several version and I tried latest to earliest to include mult-dimensional and that didn't fix it, still errors out as not defined.

 

[dhookom]

Check your spelling

    Dim conn As AODBC.Connection

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

Sorry about that...fixed the spelling and then an error that came up about syntax, now I get an error on the following section:

conn.Open "Driver={SQL Server};" & _
"Server=CTIDATA;" & _
"Database=Jobs;" & _
"UID=;" & _
"PWD=;"


The error states:

Run-time error '91'
Object variable or with block variable not set

 

[dhookom]

I believe you need:

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    conn.Open "Driver={SQL Server};" & _
                   "Server=CTIDATA;" & _
                   "Database=Jobs;" & _
                   "UID=;" & _
                   "PWD=;"

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

It does not error out now, however it does not hold the connection. When I click on the icon (world),for the sql table I had linked to, it comes up with an ODBC error. How can I get it to hold the table link. The only code I have in the load command is what I posted above. Is there something I need to add so the connection stays open as if I created the odbc connection in the control panel\administrative tools\data sources(odbc) area of my pc.

 

[dhookom]

The code is only used to create a connection in code and doesn't persist outside the code. I'm not even sure what your intention is. If you want to create linked tables to SQL Server without using a DSN, check Doug Steele's DSN-less connections

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

Thanks the "How to Programmatically Create a DSN for SQL Server with VB" link off of that "DSN-less connections" link did the trick. They may have to press a button to create the ODBC connection, but it is better than going around to everyones computer and manually doing it.

 

[MICKI0220]

I'm back again. It's works for everyone except my Windows 7 users. I think it has something to do with the different driver. Windows 7 uses the Sql native client driver. The code I am using now is for programming a DSN. It edits the registry.

Option Compare Database
Option Explicit

Private Const REG_SZ = 1 'Constant for a string variable type.
Private Const HKEY_LOCAL_MACHINE = &H80000002

Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
"RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
phkResult As Long) As Long

Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
cbData As Long) As Long

Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long






Private Sub cmdWindows7ODBC_Click()
Dim DataSourceName As String
Dim DatabaseName As String
Dim Description As String
Dim DriverPath As String
Dim DriverName As String
Dim LastUser As String
Dim Regional As String
Dim Server As String

Dim lResult As Long
Dim hKeyHandle As Long



DataSourceName = "Jobs"
DatabaseName = "Jobs"
Description = "Jobs Database"
DriverPath = "C:\Windows\System32"
LastUser = ""
Server = "CTIDATA"
DriverName = "SQL Native Client"



lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
DataSourceName, hKeyHandle)


lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
ByVal DatabaseName, Len(DatabaseName))
lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
ByVal Description, Len(Description))
lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
ByVal DriverPath, Len(DriverPath))
lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
ByVal LastUser, Len(LastUser))
lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
ByVal Server, Len(Server))



lResult = RegCloseKey(hKeyHandle)



lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
ByVal DriverName, Len(DriverName))
lResult = RegCloseKey(hKeyHandle)