When you copy the application to another workstation and the ODBC link
doesn't work, most likely the other workstation doesn't have the DSN to
your sql server set up.
Here is some code that automatically creates a System DSN for Sql Server
on an MS Windows workstation. You can place this routine in a standard
module and call it from a startup form in the Form_Load event.
'************************************************* *******
Private Const REG_SZ = 1
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
Sub createSystemODBCdsn()
'Microsoft Knowledge Base Article - 184608
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
'Specify the DSN parameters.
DataSourceName = Name of your DSN
DatabaseName = Name of the database in Sql Server
Description = simple discription - Not require
DriverPath = "C:\WINNT\System32\SQLSRV32.DLL"
LastUser = "SA" 'if SA doesn't work, use your UserID
Server = Name of the Sql Server or IP address
DriverName = "SQL Server"
'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))
'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
'************************************************* *******
Just fill in the parameters above where it says "Specify Parameters".
This will create your dsn and an entry in the registry. You can go to
Start\Settings\Control Panel\Administrative Tools\Data
Sources(ODBC)\System DSN tab to see your new dsn.
You can also go to Regedit to
HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
and you will see your dsn there too.
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!