Display Table Contents of Another Database 2

[StumblingThrough]

I'm building an application in which I hope to allow the user to link to an Access table residing in another database. I've got code that prompts the user to navigate to and select a database. What I need though is code that will then display the table contents of the other database so the user can select the table. Anyone have any ideas?

 

[dhookom]

You could use a query of the msysobjects table to get all the table names:
SELECT msysobjects.Name
FROM msysobjects
WHERE (((msysobjects.Name) Not Like "Msys*&quot AND ((msysobjects.Type)=1));


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[StumblingThrough]

Duane.

Thanks for the feedback. Having a problem with the SQL syntax. Where in your statement should the Database Name go that contains the list of tables? Should I replace "mysysobjects" with the name of the Access database?

 

[StumblingThrough]

Duane,

Sorry. I figured this one out just after I pressed "Submit Post" I first attached to the mSysObjects table of the other database and the ran the above SQL. Thanks!

 

[StumblingThrough]

Duane,

As stated above, code works, but I continually have a problem with the Access application closing when trying to attach to a table in the destination database which I selected by using the above code. I get one of those screens which says Access will have to close, etc...

 

[StumblingThrough]

Duane,

Sorry again! I determined why Access was closing. I was still attached to the MSysObjects table in the other database while I was trying to attach to the selected table. Apparently Access does not like this. I inserted a timer to wait until the other database closes (closes record locking file), and then attached to the selected table. Works like a charm! Thanks again!

 

[dhookom]

I wouldn't link to the other database. Try set your sql to something like:
SELECT msysobjects.Name
FROM msysobjects IN 'H:\Documents\Projects\Access\mydb.mdb'
WHERE (((msysobjects.Name) Not Like "Msys*&quot AND ((msysobjects.Type)=1));

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[StumblingThrough]

Duane

Interesting. I'll have to try this, but...

Actually I'm running a query from code (DoCmd.openquery "...&quot behind a form. I did this so I could display the results on screen to allow the user to select a table name from the list. I could not make this happen by using the DoCmd.runSQL "..." statement. Am I doing something wrong?

After displaying results of the query on screen, the user then types the name of the table into an inputbox, and app then links to table.

Thanks again!!

 

[RoyVidar]

Hi!

I think the most userfriendly would be if you used dhokoms very elegant sql as the rowsource of a combobox. Then the user can select from the combo (and avoid typos that are certain to happen when typing...)

Roy-Vidar

 

[dhookom]

Roy-Vidar,
My thoughts exactly except consider using a List box if it works better visually.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[StumblingThrough]

Wow. Thanks to both of you. List box works great, and much more user friendly than typing...

 

[RoyVidar]

Agree fully, (and must confess I'm a bit embarrased for not remembering/mentioning lists - guess I've done to much combos lately;-)) Thanx for the star!

Roy-Vidar