Displaying external database table on form. 3

[Moss100]

Hi - I have written the code below to open a table called 2666, which is located in a different database called mini (located on my c drive)'

On clicking a button, I want the loaded form to display the data from the external database - I do not want the data to be changed - just to be available for viewing.

At present when I click the button with my code, nothing is happening (not even errors). Can someone help me out?

Many thanks Mark



Private Sub btn_LOAD_ACCOUNT_DblClick(Cancel As Integer)

Dim rs As Recordset
Dim db As DAO.Database

Set db = OpenDatabase("c:\mini.mdb", , ReadOnly, False)

On Error Resume Next

Set rs = db.OpenRecordset("select * FROM 2666")

If Err > 0 Then
MsgBox "Can not open Account", , "ABBICUS"

Set Me.Recordset = rs

Me![Txtdate].ControlSource = "date"
Me![TxtTYPE].ControlSource = "type"
Me![TxtAmountIN].ControlSource = "Amount In"
Me![TxtAmountOUT].ControlSource = "Amount Out"

End If

On Error GoTo 0

End Sub

 

[dhookom]

Please test your hypothesis and report back. Shouldn't take too long.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

OK so it looks like the following applies.

1. Using an ADO connection I can set the recordset and then disconnect from the external database (i.e. no lock file appears on the external database using ADO connection)

2. Using a DAO connection creates a lock file on the external data base.

I'm not sure whether it is OK to use both connection types in the same database, or indeed if there is a DAO way to do this - maybe someone else can comment.

My DAO code follows if it helps anyone - though I'm sure it could be improved.

Regards Mark

Dim cnn As New ADODB.Connection
Dim ACrecordset 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:\mini.mdb"
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = CnnStr
cnn.CursorLocation = adUseNone
cnn.Open
 
Set ACrecordset = New ADODB.Recordset
With ACrecordset
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockReadOnly
 
   
    strSQL = "Select * FROM [" & Me.[txt_Ten_Gemini_Ac_Ref].Value
    strSQL = strSQL & "] ORDER BY date desc"

    
.Open strSQL, cnn

  Set ACrecordset.ActiveConnection = Nothing
 
 If .RecordCount > 0 Then
 Set Me.Recordset = ACrecordset
   
  
  Me![Txtdate].ControlSource = "Date"
  Me![TxtTYPE].ControlSource = "Type"
  Me![TxtAmountIN].ControlSource = "amount in"
  Me![TxtAmountOUT].ControlSource = "amount out"
 

  End If
   
  End With
 
 Set ACrecordset = Nothing
 cnn.Close
 Set cnn = Nothing

 

[Andrzejek]

I would use this instead:
[tt]cnn.CursorLocation = adUse[blue]Client[/blue]
[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Moss100]

Sorry - where would this go:

cnn.CursorLocation = adUseClient

Thanks Mark

 

[Andrzejek]

Set cnn = New ADODB.Connection
cnn.ConnectionString = CnnStr
[s]cnn.CursorLocation = adUseNone[/s][blue]
cnn.CursorLocation = adUseClient[/blue]
cnn.Open

Not a big deal, [tt]adUseNone[/tt] is some old stuff not used any more.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Moss100]

Thank you