INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Trying to create a ADO external connection instead of DAO

Trying to create a ADO external connection instead of DAO

(OP)
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

RE: Trying to create a ADO external connection instead of DAO

(OP)
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

RE: Trying to create a ADO external connection instead of DAO

Consider something like this:

CODE

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

RE: Trying to create a ADO external connection instead of DAO

(OP)
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

RE: Trying to create a ADO external connection instead of DAO

(OP)
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

RE: Trying to create a ADO external connection instead of DAO

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.

RE: Trying to create a ADO external connection instead of DAO

(OP)
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.

RE: Trying to create a ADO external connection instead of DAO

Had to look it up... http://support.microsoft.com/kb/184397

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

CODE

Set myrecordset.ActiveConnection = Nothing 

RE: Trying to create a ADO external connection instead of DAO

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

RE: Trying to create a ADO external connection instead of DAO

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close