Create DSN to SQL server

By Sabine.nelis ·

I have a Access database (2003) that is linked to the SQl server, through upsizing.
I made a new table on the sqlserver and linked it to my access database.
Therefor I needed to make an user dsn or System DSn. When I copy the database to another computer the one link (through DSn) does not work. Can I use Visual BAsic to make the link when opening the database? How to I have to do this?
I've already tried some stuff found on the internet, but it doesn't to the thing.

Option Explicit

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
(ByVal hwndParent As Long, ByVal fRequest As Long, _
ByVal lpszDriver As String, ByVal lpszAttributes As String) _
As Long

Private Const ODBC_ADD_SYS_DSN = 4

Public Function CreateSQLServerDSN(DSNName As String, _
ServerName As String, Database As String) As Boolean

'ServerName = Name of Server
'Database = Database to Use
'RETURNS: True if successful, false otherwise
'EXAMPLE: CreateSQLServerDSN "MyDSN", "MyServer", "MyDatabase"

Dim sAttributes As String

sAttributes = "DSN=" & DSNName & Chr(0)
sAttributes = sAttributes & "Server=" & ServerName & Chr(0)
sAttributes = sAttributes & "Database=" & Database & Chr(0)
CreateSQLServerDSN = CreateDSN("SQL Server", sAttributes)

End Function

Function autoexec()
MsgBox CreateSQLServerDSN("name dsn", "name SQlserver", "name SQlserver database")
End Function

whan I run this application, the messagebox has a value "true". When i check the ODBC there is no user dsn or system dsn made.

Is my code wrong or do I have to do something else?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -


by Tony Hopkinson In reply to Create DSN to SQL server

Did it work on the system you copied it from?
Does the DSN exist and work on the mavchine you copied it to.

Can the machine you copied it to see that SQL server, firewall, for instance. If you install sql server tools on that client can it see the server.

Code is n't going to help you until you know where the problem is, if necessary r even at all.

Different ODBC versions, different OS version, too many potential failure points at the moment, to help.

Collapse -

found the problem

by Sabine.nelis In reply to Confused


thanks for your reply, but I've fixed my problem.

Found another code and changed this to establish an User DSN (linked to my SQl server).

Option Explicit

Private Const REG_SZ = 1 'Constant for a string variable type.
Private Const HKEY_CURRENT_USER = &H80000001
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 Form_Load()

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"
DatabaseName = "name access file"
Description = "namedescription"
DriverPath = "C:\WINDOWS\system32"
LastUser = fOSUserName
Server = "name server"
DriverName = "SQL Server"

'Create the new DSN key.

DataSourceName, hKeyHandle)
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 = RegCreateKey(HKEY_CURRENT_USER, _
"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
ByVal DriverName, Len(DriverName))
lResult = RegCloseKey(hKeyHandle)
End Sub

The problem was I couldn't make a System DSN to the SQL server on a PC where the user wasn't administrator (in our company : all personal, except IT) A user DSN is possible when your not an administrator on your PC.

Sorry that I couldn't answer your earlier.

Thanks for reading my post en trying to help me out.


Related Discussions

Related Forums