Making connections to DB

[dcjones]

Hi All,

Point No1, I am not a VBA programmer, but I am trying to learn.

My Question is:

Environment: Access 2000 on MS 2000 Pro with a split database.

If I have a button on a form with the following code on the OnClick event should it work:



Private Sub Command204_Click()

Dim cnnl As ADODB.Connection
On Error GoTo connTrap

Set cnnl = New ADODB.Connection

cnnl.Open "Provider=MIcrosoft.Jet.OLEDB.4.0:" & _
"Data Source =f:\referral_be.mdb;"
cnnl.Close

cnnl.ConnectionString = "Provider=MIcrosoft.Jet.OLEDB.4.0:" & _
cnnl.ConnectionString = cnnl.ConnectionString & _
"Data Source = f:\referral_be.mdb;"
cnnl.Open

DoCmd.Echo False
Me.Visible = False
DoCmd.OpenForm "ReferralsMainAdmissionForm", OpenArgs:=Me.Name
DoCmd.Maximize
DoCmd.Echo True
connExit:

cnnl.Close
Set cnnl = Nothing
Exit Sub

connTrap:
If Err.Number = 3705 Then
Debug.Print "Closing cnnl"
cnnl.Close
Resume
Else
Debug.Print Err.Number; Err.Description
Debug.Print cnnl.Provider
Debug.Print cnnl.Provider; cnnl.ConnectionString
End If
End Sub


If someone could advise please.

Kind Regards

Dereck

 

[pono1]

Dereck,

I'm struggling a bit, trying to decipher all of your code. One thing is clear: you want to connect to an external Access file using ADO. Perhaps you can use the following as a starting point.

'Create an ADO connection object.
  Dim cnnl As ADODB.Connection
  Set cnnl = New ADODB.Connection

'Open the connection to the back-end.
  cnnl.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=f:\referral_be.mdb"

'Determine if we are actually connected.
  Debug.print cnnl.state   '1 = Connected

'Close the connection.
  cnnl.close

'Release object.
  Set cnnl = Nothing

Regards,
Tim