If on the wrong forum, I apologize.

I need to create a form with a button that runs a procedure to call a UDL in order to establish a data link connection with another Access database.

I have created the UDL text file named CallRecords.UDL: I have set the provider as 'Microsoft Office 12.0 Access Database Engine OLE DB Provider'. The data source as H:\databases\conversationrecords.accdb.
I have tested the connection and the test works.

I then created a blank form with one button and under the click event I placed a call to a function:

Function ConnectionUDL() As ADODB.Connection
Dim ConnectionString as String
Dim cnn As New ADODB.Connection
CNN.Open "File Name=D:\CallRecords.UDL"
Set ConnectionUDL = cnn
End Function

When I run the function I get an error: Run-time error '432': File name or class name not found during Autopmation operation"

Any suggestions will be greatly appreciated.

Even perhaps a cleaner/better way to make this connection. Eventually I want to be able to connect to MSSQL Server and to MYSQL.

It's a learning process for me. Thank you!

Is there a reason you need to go this route? Recommendation to avoid UDL's

I'm just asking because I would normally define the ado connection string in Access and avoid the risk and extra work of the UDL.

OK, but it's the only way I know of. If there's a better simpler way, I don't know of it and would really appreciate the code to implement it.

Thanks!

OK. If anyone cares. I have worked out this much and so far it works to the point of being able to access any Access Dababase. Just change the Data Source to what you might need.

Under the click event:

Private Sub CallRecord_Click()
Dim cnn as New ADODB.Connection
cnn = "Provider = Microsoft Office 12.0 Access Database Engine OLE DB provider; Data Source=H:\databases\conversationrecords.accdb;"
cnn.open
Dim rst as New ADODB.Recordset
rst.open "tblCallCenter", cnn, adOpenDynamic, adLockOptimistic

....... any other code desired

End Sub

Not sure if this is the best and easiest way, yet, but it's all I could come up with after many many hours of trial and error. Seems to work though. Now I'll post my other question.

(IMG:style_emoticons/default/ohyeah.gif)

Congratulations on resolving your requirement and thanks for posting back the results.
It's often the case that the solutions we find for ourselves are the most satisfying.

"Best" and "easiest" tend to be subjective terms. This code works and it's efficient.
Continued success with your project.