473,703 Members | 2,820 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 6183
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_redic k@Mind_NoSpam_s pring.com> wrote in message news:<3F8EEAF9. C376F9FA@Mind_N oSpam_spring.co m>...
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_Cl ick()
Dim strSQLServerDLL Path As String
Dim strDefaultDB As String
If optMaster Then
strDefaultDB = "master"
Else
If Len(txtDefaultD B) = 0 Then
MsgBox "Must specify other db"
Exit Sub
Else
strDefaultDB = txtDefaultDB.Te xt
End If
End If
strSQLServerDLL Path = RegistryGetKeyV alue(HKEY_LOCAL _MACHINE, "SOFTWARE\ODBC\ ODBCINST.INI\SQ L Server", "Driver")
'system dsn, same name as server
'delete the key and values if exists
RegDeleteKey HKEY_LOCAL_MACH INE, "SOFTWARE\ODBC\ ODBC.INI\Server 1"
RegistryDeleteV alue HKEY_LOCAL_MACH INE, "SOFTWARE\ODBC\ ODBC.INI\ODBC Data Sources", "Server1"
'create new dsn
CreateDSN "Server1", "SQL Server Databases on Server1", _
"Server1", strDefaultDB, _
"SQL Server", strSQLServerDLL Path, _
"Network", "Yes"
'system dsn, name is different than server
'delete the key and values if exists
RegDeleteKey HKEY_LOCAL_MACH INE, "SOFTWARE\ODBC\ ODBC.INI\Produc tionDB"
RegistryDeleteV alue HKEY_LOCAL_MACH INE, "SOFTWARE\ODBC\ ODBC.INI\ODBC Data Sources", "Production DB"
CreateDSN "Production DB", "Production SQL Server Databases on Server2", _
"Server2", strDefaultDB, _
"SQL Server", strSQLServerDLL Path, _
"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_MACH INE = &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_OUTOFMEMO RY = 6
Public Const ERROR_ARENA_TRA SHED = 7
Public Const ERROR_ACCESS_DE NIED = 8
Public Const ERROR_INVALID_P ARAMETERS = 87
Public Const ERROR_NO_MORE_I TEMS = 259

Public Declare Function RegCreateKey Lib "advapi32.d ll" Alias _
"RegCreateK eyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
phkResult As Long) As Long

Public Declare Function RegSetValueEx Lib "advapi32.d ll" 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.d ll" _
(ByVal hKey As Long) As Long

Public Declare Function RegDeleteKey Lib "advapi32.d ll" Alias _
"RegDeleteK eyA" (ByVal hKey As Long, ByVal lpSubKey As String) As Long

Public Declare Function RegDeleteValue Lib "advapi32.d ll" Alias _
"RegDeleteValue A" (ByVal lngHKey As Long, ByVal lpValueName As String) As Long

Private Declare Function RegQueryValueEx String Lib "advapi32.d ll" Alias _
"RegQueryValueE xA" (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 RegQueryValueEx Long Lib "advapi32.d ll" Alias _
"RegQueryValueE xA" (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 RegQueryValueEx NULL Lib "advapi32.d ll" Alias _
"RegQueryValueE xA" (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.d ll" Alias _
"RegOpenKey ExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As _
Long) As Long

Public Sub CreateDSN(DataS ourceName 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"
'TrustedConnect ion = <Yes or No>
'Create the new DSN key.

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

'Set the values of the new DSN key.

lResult = RegSetValueEx(h KeyHandle, "Database", 0&, REG_SZ, _
ByVal DatabaseName, Len(DatabaseNam e))
lResult = RegSetValueEx(h KeyHandle, "Descriptio n", 0&, REG_SZ, _
ByVal Description, Len(Description ))
lResult = RegSetValueEx(h KeyHandle, "Driver", 0&, REG_SZ, _
ByVal DriverPath, Len(DriverPath) )
lResult = RegSetValueEx(h KeyHandle, "LastUser", 0&, REG_SZ, _
ByVal LastUser, Len(LastUser))
lResult = RegSetValueEx(h KeyHandle, "Server", 0&, REG_SZ, _
ByVal Server, Len(Server))
lResult = RegSetValueEx(h KeyHandle, "Trusted_Connec tion", 0&, REG_SZ, _
ByVal TrustedConnect, Len(TrustedConn ect))

'Close the new DSN key.

lResult = RegCloseKey(hKe yHandle)

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

lResult = RegCreateKey(HK EY_LOCAL_MACHIN E, _
"SOFTWARE\ODBC\ ODBC.INI\ODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(h KeyHandle, DataSourceName, 0&, REG_SZ, _
ByVal DriverName, Len(DriverName) )
lResult = RegCloseKey(hKe yHandle)

End Sub

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

Dim lResult As Long
Dim hKeyHandle As Long

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

End Sub

Public Function RegistryGetKeyV alue(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(ln gRootKey, sKeyName, 0, _
KEY_ALL_ACCESS, hKey)
lRetVal = QueryValueEx(hK ey, sValueName, vValue)
'MsgBox vValue
RegCloseKey (hKey)
RegistryGetKeyV alue = vValue

End Function

Private Function QueryValueEx(By Val 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 QueryValueExErr or

' Determine the size and type of data to be read
lrc = RegQueryValueEx NULL(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 = RegQueryValueEx String(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 = RegQueryValueEx Long(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

QueryValueExExi t:
QueryValueEx = lrc

Exit Function

QueryValueExErr or:
Resume QueryValueExExi t

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
1888
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 will do fine. 1. PHP includes a native ODBC module that is available without any extra installs i.e. it's always there, correct ? 2. I can use PHP on the MS-windows platform to access any ODBC compliant d/b that there is a driver for, correct ?
6
6773
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 ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:
3
75901
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 i´m a rather newbie, so sorry for unexact question. I tried in beforehand to find some for me relevant information on the IBM or MS side and on Google-groups, but i failed (maybe i´m just to
2
6185
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 downloaded the IBM DB2 client i think which after the installation i saw the ODBC Driver added for IBM DB2. However i couldn't create a system dsn that worked when i tried to connect to an IBM DB2 database.
2
2179
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 2003 and found a surprise. There is a certain case where we allow the MDI app to close the database and the user can later request the open dialog. This normally calls the CDatabase open with the flags that allow the ODBC Open Dialog
0
1639
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 drivers without any intervention by the user…??? I include the MyODBC 3.51 drivers installer in my project. When a user installs the application, the VB.net installer first checks if the drivers are installed. If they are not found on the user computer, it stops the global VB.net installation and...
7
3058
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 connect string in A97 is FoxPro 2.0;HDR=NO;IMEX=2;DATABASE=C:\Test It's really easy to connect to those tables in A97. I'm having difficulties in A2003. I'm trying to follow the instructions in http://support.microsoft.com/kb/824264/.
8
3039
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 Databases from the Files of Type dropdown. As soon as I select ODBC Databases from the Files of Type dropdown, the entire dialog box disappears. No error message. Just disappears, and I'm back at the database window. I have tried this with other databases, including a brand new blank one, and...
1
2795
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 symcjit.dll to symcjit_old.dll). All the components appeared to install. I used OUI to install the 8i ODBC drivers but when I go to ADMINISTRATIVE TOOLS/DATA SOURCES >>>SYSTEM DNS tab and try to add the 8i ODBC driver set it does not appear in the list Using Oracle's KB I found a list of all...
0
8759
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8669
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9251
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9017
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8963
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4433
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3125
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2453
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.