Trying to create a ADO external connection instead of DAO

[Moss100]

Hello,

I am trying to create a recordset that disconnects from the external database once it has its data.

I have a DAO code which works, but to my disappointment it keeps an active lock on the external Gemini database.

I now understand that DAO can not disconncet and that I should be using a ADO connection.

I want to ensure as far as possible that there is no chance of the external database being corrupted or locked.

On this basis I think a disconnected, read only recordset is what I am after. I would greatly apprevciate being shown how to achive this and any other tips are very welcome.

The code I use currently is below.

Many thanks Mark



Option Compare Database

Dim wrkJet As Workspace
Dim dbsGemini As DAO.Database, rstGemini As DAO.Recordset
Private Sub BTN_LOAD_ACCOUNT_DblClick(Cancel As Integer)
'Create Microsoft Jet Workspace object.
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

'Open Database object from saved Microsoft Jet database for exclusive?? use
Set dbsGemini = wrkJet.OpenDatabase("M:\Gemini.mdb", , False)

On Error Resume Next


Set rstGemini = _
dbsGemini.OpenRecordset("SELECT * " & _
"FROM " & Me.[Txttable] & " ORDER BY date desc", _
dbOpenSnapshot)

If err > 0 Then
MsgBox "Can not open Account"
Else

Set Me.Recordset = rstGemini

Me![Txtdate].ControlSource = "Date"
Me![TxtTYPE].ControlSource = "Type"
Me![TxtAmountIN].ControlSource = "Amount In"
Me![TxtAmountOUT].ControlSource = "Amount Out"

Set rstGemini = Nothing
Set dbsGemini = Nothing
End If

On Error GoTo 0
End Sub

 

[Moss100]

OK - I have cobbled this together - but need to display the results on my form ACCOUNT

I am not sure how to proceed.

On my form account I have a unbound text box called txtdate and would like the field date from the recordset to populate it.

Thanks Mark

Private Sub Command27_DblClick(Cancel As Integer)

'build a recordet from external.mdb database

Dim CnnStr As String
CnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
CnnStr = CnnStr + "User ID=Admin;"
CnnStr = CnnStr + "Data Source=C:\temp\gemini.mdb"

Dim cnn2 As New ADODB.Connection
Dim myrecordset As New ADODB.Recordset
myrecordset.ActiveConnection = CnnStr
myrecordset.Open "select * FROM [1250]"

Set Me.Recordset = myrecordset

 

[Andrzejek]

Consider something like this:

Dim cnn2 As New ADODB.Connection
Dim myrecordset As ADODB.Recordset
Dim CnnStr As String
Dim strSQL As String

CnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
CnnStr = CnnStr + "User ID=Admin;"
CnnStr = CnnStr + "Data Source=C:\temp\gemini.mdb"

Set cnn2 = New ADODB.Connection
cnn2.ConnectionString = CnnStr
cnn2.CursorLocation = adUseNone
cnn2.Open

Set myrecordset = New ADODB.Recordset
With myrecordset
    .CursorType = adOpenForwardOnly
    .CursorLocation = adUseClient
    .LockType = adLockReadOnly

    strSQL = "SELECT * FROM tblSomeTable WHERE ..."

    .Open strSQL, cnn2

    If .RecordCount > 0 Then
        txtdate = !SomeFieldFromYourTable.Value
    End If

    .Close
End With
Set myrecordset = Nothing

cnn2.Close
Set cnn2 = Nothing

Code not tested

Have fun.

---- Andy

 

[Moss100]

Thanks for your help Andy. I tried the code (copy below) and it is obviously connecting, but only one record is being returned. Can you / anyone help further?

Many thanks Mark

Private Sub BTN_LOAD_ACCOUNT_DblClick(Cancel As Integer)
Dim cnn2 As New ADODB.Connection
Dim myrecordset As ADODB.Recordset
Dim CnnStr As String
Dim strSQL As String

CnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
CnnStr = CnnStr + "User ID=Admin;"
CnnStr = CnnStr + "Data Source=C:\temp\gemini.mdb"

Set cnn2 = New ADODB.Connection
cnn2.ConnectionString = CnnStr
cnn2.CursorLocation = adUseNone
cnn2.Open

Set myrecordset = New adOpenForwardOnly
With myrecordset
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly

strSQL = "SELECT * FROM 1250"

.Open strSQL, cnn2

If .RecordCount > 0 Then
Txtdate = !Date.Value
End If

.Close
End With
Set myrecordset = Nothing

cnn2.Close
Set cnn2 = Nothing
End Sub

 

[Moss100]

Hello - I now have this code working OK.

Is there anything that I should add or take away from it to ensure reliability?

Thanks Mark

Dim cnn As New ADODB.Connection
Dim myrecordset As ADODB.Recordset
Dim CnnStr As String
Dim strSQL As String

CnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
CnnStr = CnnStr + "User ID=Admin;"
CnnStr = CnnStr + "Data Source=C:\temp\gemini.mdb"

Set cnn = New ADODB.Connection
cnn.ConnectionString = CnnStr
cnn.CursorLocation = adUseNone
cnn.Open

Set myrecordset = New ADODB.Recordset
With myrecordset
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly

strSQL = "SELECT * FROM 1250"

.Open strSQL, cnn

If .RecordCount > 0 Then
Set Me.Recordset = myrecordset

Me![Txtdate].ControlSource = "Date"
Me![TxtTYPE].ControlSource = "Type"
Me![TxtAmountIN].ControlSource = "Amount In"
Me![TxtAmountOUT].ControlSource = "Amount Out"
Me![TxtAdjustmentNote].ControlSource = "Adjustment Note"
Me![TxtTenantBAL].ControlSource = "Tenant balance"
Me![TxtCurrentBAL].ControlSource = "Current balance"
Me![TxtDepositBAL].ControlSource = "Deposit balance"

End If
.Close

End With

Set myrecordset = Nothing
cnn.Close
Set cnn = Nothing

End Sub

 

[lameid]

I've not used a disconnected recordset but I think once you retrieve the data (set the object) you then set the connection property to nothing / "" or something to make it disconnected. Then Writes do not occur back to the database until you reconnect or set the connection again.... As for the specifics I just don't remember as I only read about it. I've been hoping someone else would jump in with the exact answer.

 

[Moss100]

Yes hopefully this is closing connections


Close

End With

Set myrecordset = Nothing
cnn.Close
Set cnn = Nothing


I have watched a connection to the back end database and no lock file is created so it looks like it is doing its stuff - but this is my first attempt so I would welcome comments and enhancements.

Regards mark.

 

[lameid]

Had to look it up...

http://support.microsoft.com/kb/184397

You are missing clearing the connection for the recordset...

Set myrecordset.ActiveConnection = Nothing

 

[Moss100]

Hajj ok where would I put that - right at the end?

 

[lameid]

Anytime before you close the connection and after you open the recordset.

Also, You don't need to close the recordset (.close) as the recordset is disconnected. Then your form should be truly continuous. You probably should disconnect the recordset before setting the recordsource to the recordset in case there it is set byval instead of byref.