Setting ROWSOURCE to an External Table

[Roger Lord]

Not sure if this is the proper forum, but here it goes:

I am writing an Access 2003 application in which I'm trying to
set a listbox's rowsource to a table contained in an external
database. The external table is dynamically created in this
application and is used to show the results of a query. I can do
this easily by filling a table in application itself (i.e., a
table in the .mdb file I am writing), but repeated queries cause
the size of the .mdb file to grow and grow. In its final form,
the application will be compiled into a .mde file for a group of
users. If compacting the database was easy to do in the
application itself, that is the way I would go; but my users
don't know anything about Access and I can't rely on them to
compact the file.

Instead, I create an external database (C:\Temp\Results.mdb) and
then create a table within that database (RPT_BUFFER), and then I
fill it with data. This part works fine. The table gets created
and gets filled with data exactly as planned. When I exit the
application, the external database/table are deleted. That's my
method of compacting.
The problem comes when I try to set the rowsource of a listbox in
my main application to the external table just created.

Here's how I'm trying to do it (my listbox is named lbxResults):


Dim mSQL as String

lbxResults.RowSource = ""

mSQL = "SELECT * FROM RPT_BUFFER IN 'C:\Temp\Results.mdb' "

lbxResults.RowSource = mSQL



Nothing bombs, but all I see in the listbox is the column names
(I set the listbox to show the column names).

Any ideas as to what is going wrong?

If this is not the proper forum, my apologies. Please tell me
which is the correct one.

Thanks.

Roger

 

[Chris O'C via AccessMonster.com]

What event are you putting this code in? The form's Current and Open events
should work. In a button's click event, too.

Chris
Microsoft MVP

 

[Roger Lord]

Chris,

I figured out what was causing the problem. In the step where I
created the database and table, I had to close the database after
its creation and filling with data before the listbox rowsource
was linked to the table. As soon as I closed the database,
everything worked fine.

In direct answer to your question, however, the event that the
code is in is "Private Sub Form_Load()" of a new form that I open
as soon as I am done creating the database/table.

Thanks for the help.

Sincerely,
Roger

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

What event are you putting this code in? The form's Current and
Open events
should work. In a button's click event, too.

Chris
Microsoft MVP