[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Create a DSN for an Access DB in Code

I downloaded some code that creates a DSN for an SQL Server database, if a supplied DSN Name doesn't exist.  The code works great, but I need to do the same for an Access Database DSN.  Pasted in this question are the SQL Server specific functions, etc..  Can anyone provide the Access equivalent?

SQL Server ODBC API:
-----------------------
Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
    (ByVal hwndParent As Long, _
    ByVal fRequest As Integer, _
    ByVal lpszDriver As String, _
    ByVal lpszAttributes As String) As Long

.
.
.
Function to create an SQL Server DSN:
----------------------------------------
lngResult = SQLConfigDataSource(0, _
   ODBC_ADD_SYS_DSN, _
   "SQL Server", _
   "DSN=" & JDS_DSN_name & Chr(0) & _
   "Server=" & JDS_Server_name & Chr(0) & _
   "Database=SvCvMarketing" & Chr(0) & _
   "UseProcForPrepare=Yes" & Chr(0) & _
   "Description=MDTS Database" & Chr(0) & Chr(0))
 
...Or a text file for the API viewer on ODBCJT32.DLL?

TIA, KMAN
0
KMAN
Asked:
KMAN
  • 4
  • 3
1 Solution
 
paaskyCommented:
Hello KMAN,

If you're using Access 97, you could use RegisterDatabase method:

I modified the sample (made for SQL Server) that it adds Access DSN:

Sub RegisterDatabaseX()

    Dim dbsRegister As Database
    Dim strDescription As String
    Dim strAttributes As String
    Dim errLoop As Error

    ' Build keywords string.
    strDescription = InputBox("Enter a description " & _
        "for the database to be registered.")
    strAttributes = "Database=MyDatabase" & _
        vbCr & "Description=" & strDescription

    ' Update Windows Registry.
    On Error GoTo Err_Register

DBEngine.RegisterDatabase "MyDatabase", "Microsoft Access Driver (*.mdb)", _
        True, strAttributes
    On Error GoTo 0

    MsgBox "Use regedit.exe to view changes: " & _
        "HKEY_CURRENT_USER\" & _
        "Software\ODBC\ODBC.INI"

    Exit Sub

Err_Register:

    ' Notify user of any errors that result from
    ' the invalid data.
    If DBEngine.Errors.Count > 0 Then
        For Each errLoop In DBEngine.Errors
            MsgBox "Error number: " & errLoop.Number & _
                vbCr & errLoop.Description

Next errLoop
    End If

    Resume Next

End Sub

Regards,
Paasky
0
 
paaskyCommented:
Here are more attributes you might like to set too (DBQ is important!):

Database = <DSN>
Description = <Description>
DBQ=<Database filename>
UID=<User id>

for eg.
    strAttributes = "Database=MyDatabase2" & _
        vbCr & "Description=" & strDescription & _
        vbCr & "DBQ=G:\Data\Customers.mdb"

Paasky

0
 
KMANAuthor Commented:
Yes, I found the DBQ value to be quite necessary.

Thanks.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
paaskyCommented:
KMAN, glad I could help. Sorry I forgot to add DBQ parameter in the first code - it's 1:20 AM here and feeling a bit tired already...

You can use this same code to register any data source type you like. If driver parameters are not known, just create "sample" DSN first with ODBC Administrator. Then check your registry what keys have been added and place them into the Attributes.

Paasky
0
 
KMANAuthor Commented:
That's what I did, the Sample idea. Great minds think alike!

Hey any way to make this a System DSN instead of User DSN?
0
 
paaskyCommented:
You should use the services of "odbccp32.dll" library. I had to do some extra research because ODBC_ADD_SYS_DSN was constant and I didn't know its value...

This should do the trick:

Public Sub AddSystemDsn()
Dim LngResult As Long

LngResult = SQLConfigDataSource(0, _
   4, _
   "Microsoft Access Driver (*.mdb)", _
   "DSN=Test" & Chr(0) & _
   "DBQ=g:\exchange\sample1.mdb" & Chr(0) & _
   "Description=My database description" & Chr(0) & Chr(0))

    Debug.Print LngResult

End Sub

New registry data for System DSN should be written under  \HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI

Hope this helps,
Paasky
0
 
KMANAuthor Commented:
Thanks, I'll give that a try.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now