Remote query external MDB without locking it

[aaronlglover]

--------------------------------------------------------------------------------

Alright guys,

I am beating my head against a wall with this one. I am a new access programmer . Here is what I have; One MDB that has a couple of tables for storing data and another MDB that will be used as a front end that has a couple of forms with listboxs etc. What I am trying to find out how to do is remote query the table MDB from the form MDB without linking tables (linking tables will record lock the tables when the forms are open.)

I have tried everything I can think of...when I set the recordsource of the listbox to remote query the table (using the IN function) it locks the other MDB. When I use vba and set the rowsource of the listbox it locks the MDB. I tried using ADODB and the listbox additem property but that took forever.

In excel VBA and userforms I could use the ADODB and use the columncount and column listbox to drop recordsets in but when I do that in access I get a Compile Error: Arguement not optional. I don't know what to do...anyone have any recommendations?

Here is the VBA I am using for the column method

Private Sub List147_GotFocus()

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim rcArray As Variant
Dim sSQL As String
Dim NoOfRecords


sSQL = "SELECT * FROM qry_queue IN 'C:\Documents and Settings\aaron.glover\My Documents\attendance.mdb';"

Set cnt = New ADODB.Connection
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\aaron.glover\My Documents\attendance.mdb'"
cnt.Execute (sSQL)

With rst
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With


Me.List147.ColumnCount = rst.Fields.Count

Me.List147.Column = rs.GetRows(NoOfRecords)

rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Sub

 

[PWise]

how about
Me.List147.ColumnCount = rst.Fields.Count
set me.Me.List147.recordset = rst