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?

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, _
   "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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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\" & _

    Exit Sub


    ' 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

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"



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KMANAuthor Commented:
Yes, I found the DBQ value to be quite necessary.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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.

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?
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,
KMANAuthor Commented:
Thanks, I'll give that a try.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.