472,328 Members | 1,551 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,328 software developers and data experts.

ODBC on new installs

Maybe a dumb question - I'm new to ODBC. How do I install an Access
..mde file on a user's workstation and create the ODBC connection to
the backend SQL Server database without having to go through the
Administrative Tools>Data SOurces (ODBC) menu? IS there a way to do
this with VBA code from my installer disk, or by simply copying a file
onto the user's PC?
Any help is appreciated.
LQ
Nov 12 '05 #1
3 6053
TR
A couple of options.
You could create a registry merge file from an export your ODBC settings
from the registry. Edit them to include only the sections of interest.
Then just merge the reg file on the workstations. Works pretty well if the
OS is the same. May have issues if installing to different OS.

or, you could write a small "helper" application that creates the
neccessary registry entries via VBA code, or in VB.

Do you have VB6? I have a small vb project that that does just that. we
needed to update many users system dsn's when we changed servers, so I
made a small exe they could run to easily do it. I could send you the
source code, nothing fancy, its quite simple, the dsn settings are
hardcoded but easily edited (I planned to put them in an ini file that the
app would read, so the settings could be easily changed. No time...).
Assuming the users have the neccessary sql server drivers installed, its
usually just a matter of getting the registry data in place.

Lauren Quantrell wrote:
Maybe a dumb question - I'm new to ODBC. How do I install an Access
.mde file on a user's workstation and create the ODBC connection to
the backend SQL Server database without having to go through the
Administrative Tools>Data SOurces (ODBC) menu? IS there a way to do
this with VBA code from my installer disk, or by simply copying a file
onto the user's PC?
Any help is appreciated.
LQ


Nov 12 '05 #2
Registry Merge File? I've never worked with that before. I've never
written a VBA app to change th registry settings (but would like to
learn if you have an example.) I don't have VB, just VBA and would
like to keep it to that. The file you mention in VB6. Can I cut and
paste it into VBA?
LQ


TR <t_NoSpam_redick@Mind_NoSpam_spring.com> wrote in message news:<3F8EEAF9.C376F9FA@Mind_NoSpam_spring.com>...
A couple of options.
You could create a registry merge file from an export your ODBC settings
from the registry. Edit them to include only the sections of interest.
Then just merge the reg file on the workstations. Works pretty well if the
OS is the same. May have issues if installing to different OS.

or, you could write a small "helper" application that creates the
neccessary registry entries via VBA code, or in VB.

Do you have VB6? I have a small vb project that that does just that. we
needed to update many users system dsn's when we changed servers, so I
made a small exe they could run to easily do it. I could send you the
source code, nothing fancy, its quite simple, the dsn settings are
hardcoded but easily edited (I planned to put them in an ini file that the
app would read, so the settings could be easily changed. No time...).
Assuming the users have the neccessary sql server drivers installed, its
usually just a matter of getting the registry data in place.

Lauren Quantrell wrote:
Maybe a dumb question - I'm new to ODBC. How do I install an Access
.mde file on a user's workstation and create the ODBC connection to
the backend SQL Server database without having to go through the
Administrative Tools>Data SOurces (ODBC) menu? IS there a way to do
this with VBA code from my installer disk, or by simply copying a file
onto the user's PC?
Any help is appreciated.
LQ

Nov 12 '05 #3
TR
Should be able to use this in VBA.
Below is the function I use in a command button click event on a form to create the DSN(s). Below that is the
module code that does the work. I use an option box and text box to allow a different default database to be
selected, you may not need that. Modify the call to CreateDSN to suit.

'******************Begin Form Code
Private Sub cmdCreateDSN_Click()
Dim strSQLServerDLLPath As String
Dim strDefaultDB As String
If optMaster Then
strDefaultDB = "master"
Else
If Len(txtDefaultDB) = 0 Then
MsgBox "Must specify other db"
Exit Sub
Else
strDefaultDB = txtDefaultDB.Text
End If
End If
strSQLServerDLLPath = RegistryGetKeyValue(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBCINST.INI\SQL Server", "Driver")
'system dsn, same name as server
'delete the key and values if exists
RegDeleteKey HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\Server1"
RegistryDeleteValue HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", "Server1"
'create new dsn
CreateDSN "Server1", "SQL Server Databases on Server1", _
"Server1", strDefaultDB, _
"SQL Server", strSQLServerDLLPath, _
"Network", "Yes"
'system dsn, name is different than server
'delete the key and values if exists
RegDeleteKey HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ProductionDB"
RegistryDeleteValue HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", "ProductionDB"
CreateDSN "ProductionDB", "Production SQL Server Databases on Server2", _
"Server2", strDefaultDB, _
"SQL Server", strSQLServerDLLPath, _
"Network", "Yes"

MsgBox "DSN's Created, please test and confirm configuration.", vbInformation, "DSN's Created"

End Sub
'******************End Form Code
Here is the code for a module (basDSN):
'******************Begin Module Code
Option Explicit

Public Const HKEY_LOCAL_MACHINE = &H80000002
Public Const REG_SZ = 1 'Constant for a string variable type.
Public Const REG_DWORD As Long = 4

Public Const ERROR_NONE = 0
Public Const ERROR_BADDB = 1
Public Const ERROR_BADKEY = 2
Public Const ERROR_CANTOPEN = 3
Public Const ERROR_CANTREAD = 4
Public Const ERROR_CANTWRITE = 5
Public Const ERROR_OUTOFMEMORY = 6
Public Const ERROR_ARENA_TRASHED = 7
Public Const ERROR_ACCESS_DENIED = 8
Public Const ERROR_INVALID_PARAMETERS = 87
Public Const ERROR_NO_MORE_ITEMS = 259

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

Public 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

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

Public Declare Function RegDeleteKey Lib "advapi32.dll" Alias _
"RegDeleteKeyA" (ByVal hKey As Long, ByVal lpSubKey As String) As Long

Public Declare Function RegDeleteValue Lib "advapi32.dll" Alias _
"RegDeleteValueA" (ByVal lngHKey As Long, ByVal lpValueName As String) As Long

Private Declare Function RegQueryValueExString Lib "advapi32.dll" Alias _
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
As String, lpcbData As Long) As Long

Private Declare Function RegQueryValueExLong Lib "advapi32.dll" Alias _
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
String, ByVal lpReserved As Long, lpType As Long, lpData As _
Long, lpcbData As Long) As Long

Private Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias _
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
As Long, lpcbData As Long) As Long

Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias _
"RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As _
Long) As Long

Public Sub CreateDSN(DataSourceName As String, Description As String, _
Server As String, DatabaseName As String, _
DriverName As String, DriverPath As String, _
LastUser As String, TrustedConnect As String)

Dim lResult As Long
Dim hKeyHandle As Long

'Specify the DSN parameters.

'DataSourceName = "<the name of your new DSN>"
'DatabaseName = "<name of the database to be accessed by the new DSN>"
'Description = "<a description of the new DSN>"
'DriverPath = "<path to your SQL Server driver>"
'LastUser = "<default user ID of the new DSN>"
'Server = "<name of the server to be accessed by the new DSN>"
'DriverName = "SQL Server"
'TrustedConnection = <Yes or No>
'Create the new DSN key.

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

'Set the values of the new DSN key.

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 = RegSetValueEx(hKeyHandle, "Trusted_Connection", 0&, REG_SZ, _
ByVal TrustedConnect, Len(TrustedConnect))

'Close the new DSN key.

lResult = RegCloseKey(hKeyHandle)

'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
'Specify the new value.
'Close the key.

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)

End Sub

Public Sub RegistryDeleteValue( _
lngRootKey As Long, _
strKeyName As String, _
strValueName As String)

Dim lResult As Long
Dim hKeyHandle As Long

' Open the key
lResult = RegCreateKey(lngRootKey, strKeyName, hKeyHandle)
' If the key was opened successfully, then delete it
If lResult = 0 Then
lResult = RegDeleteValue(hKeyHandle, strValueName)
End If
lResult = RegCloseKey(hKeyHandle)

End Sub

Public Function RegistryGetKeyValue(lngRootKey As Long, sKeyName As String, sValueName As String)

Dim lRetVal As Long 'result of the API functions
Dim hKey As Long 'handle of opened key
Dim vValue As Variant 'setting of queried value
Const KEY_ALL_ACCESS = &H3F

lRetVal = RegOpenKeyEx(lngRootKey, sKeyName, 0, _
KEY_ALL_ACCESS, hKey)
lRetVal = QueryValueEx(hKey, sValueName, vValue)
'MsgBox vValue
RegCloseKey (hKey)
RegistryGetKeyValue = vValue

End Function

Private Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As _
String, vValue As Variant) As Long

Dim cch As Long
Dim lrc As Long
Dim lType As Long
Dim lValue As Long
Dim sValue As String

On Error GoTo QueryValueExError

' Determine the size and type of data to be read
lrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)
If lrc <> ERROR_NONE Then Error 5

Select Case lType
' For strings
Case REG_SZ:
sValue = String(cch, 0)

lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType, _
sValue, cch)
If lrc = ERROR_NONE Then
vValue = Left$(sValue, cch - 1)
Else
vValue = Empty
End If
' For DWORDS
Case REG_DWORD:
lrc = RegQueryValueExLong(lhKey, szValueName, 0&, lType, _
lValue, cch)
If lrc = ERROR_NONE Then vValue = lValue
Case Else
'all other data types not supported
lrc = -1
End Select

QueryValueExExit:
QueryValueEx = lrc

Exit Function

QueryValueExError:
Resume QueryValueExExit

End Function
'******************End Module Code

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Bob | last post by:
OK, PHP newbie here, did a search, but I can't see the forest for the trees now. I need a sanity check to see if I've got this right. Short answers...
6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) -...
3
by: Klaus Oberdalhoff [MVP] | last post by:
Hi, Frontend: MS Access XP on Win XP Backend AIX: DB2 ... (7.1) Hi, to be honest, concerning the db2 im a rather newbie, so sorry for...
2
by: serge | last post by:
Do I have to use the DB2 Configuration Assistant in order to create an ODBC System DSN? I had a windows computer with no IBM DB2 installed. I...
2
by: David | last post by:
Hello, I have an application that has been running for years and had little problem with it. We've been retesting the latest code under Windows...
0
by: simf1 | last post by:
Hi! I would like to know if I can create a setup File (for my VB.net application linked to a mysql DB) that would install automatically MyODBC...
7
by: Salad | last post by:
I am converting an application from A97 to A2003. I have 2 tables created by another application as a Foxpro.dbf. The table has no index. The...
8
by: Neil | last post by:
I just started using Access 2003, and I can't link a SQL Server table. I right-click on the database window; select Link Tables; and select ODBC...
1
by: jimbo905 | last post by:
My secretaries HD crashed - bummer. I got a new HD running XP pro with SP3. I have installed 8i client using the OUI (at first I had to rename...
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.