Get information from other tables

[Biggles]

I have some tables in one database i would like to reference from another. I
have used linked tables in the past, but I don't like the way this will lock
out other developers from using that database. I thought this should work,
but the list24.rowsource = rs1 line is choking on a mismatch error.

Dim art_db As String
Dim conn_art As ADODB.Connection
Dim ssql1 As String
Dim rs1 As ADODB.Recordset

art_db = "L:\COMMON\Audit Resource & Training (ART)\auditresource.mdb"

Set conn_art = New ADODB.Connection
conn_art.ConnectionString = "provider=microsoft.jet.oledb.4.0;data
source=" & "'" & art_db & "'"
conn_art.Open

Set rs1 = New ADODB.Recordset

ssql1 = "SELECT tblAuditorInformation.strLANID,
tblAuditorInformation.strFirstName, " & _
"tblAuditorInformation.strMiddleName,
tblAuditorInformation.strLastName " & _
"FROM tblAuditorInformation " & _
"WHERE (((tblAuditorInformation.strAuditGroup)='Professional
Practices Group') " & _
"AND ((tblAuditorInformation.dtmTerminationDate) Is Null))"

rs1.Open ssql1, conn_art

Me.List24.RowSource = rs1

conn_art.close

Set conn_art = Nothing

Basically, I want, when the form loads, to populate this combobox with
information I query from another database, but I don't want to use linked
tables to do it. I want to open the connection, get the information, keep
the information, and close the connection. Can that be done with minor
tweaking/correcting of what I have here.

 

[John W. Vinson]

I have some tables in one database i would like to reference from another. I
have used linked tables in the past, but I don't like the way this will lock
out other developers from using that database.

What makes you think linked tables would keep anyone from using the database!?

Sure, they wouldn't be able to change the table's *structure* while it's in
use, but if you properly have a backend database containing only tables, that
should rarely or never occur.

 

[Biggles]

We don't have that type of setup yet. Different programmers without
established standards, etc.

What I am suggesting is not possible? Or is it so unwieldy as to not work
at all?

 

[John W. Vinson]

We don't have that type of setup yet. Different programmers without
established standards, etc.

What I am suggesting is not possible? Or is it so unwieldy as to not work
at all?

I guess I'm not at all clear what you're suggesting, or what kind of setup you
DO have. If you have multiple programmers all working on the same unitary
database then... yes, you're in trouble.

Any multiuser application should ABSOLUTELY be split into a frontend (forms,
reports, code, etc.) and backend (just tables). See
http://www.granite.ab.ca/access/splitapp.htm
for a thorough discussion. Each developer can then work on THEIR OWN COPY of
the frontend without mutual interference; there will need to be some sort of
meeting of the minds about how to incorporate each developer's additions into
a (*WELL BACKED UP!!!*) master copy of the database.