Getting a List of Tables in a Remote Database.

[Doug Sanders]

I have a remote database on a server (mapped drive) that has a group of
tables that are created, used, then deleted.

First I want to get a list of the tables in that database into a form, and
then create a mailmerge letter from the table I select from the list.


I hope I made myself clear.

Thanks for any help.

Doug Sanders

 

[PieterLinden via AccessMonster.com]

I have a remote database on a server (mapped drive) that has a group of
tables that are created, used, then deleted.

First I want to get a list of the tables in that database into a form, and
then create a mailmerge letter from the table I select from the list.

I hope I made myself clear.

Thanks for any help.

Doug Sanders

You always merge with a table (and not a query/view) as the source for your
merge?

At any rate, this is pretty easy...
You could link to the tables in the remote database... except the fact that
they're constantly created and deleted is going to make things hard. If the
structures are constant, you can just delete the contents of the tables -
because then at least if you create queries against your linked tables,
they'll always work. They may not return data, but at least the tables will
exist so you can query them. Then you can just add the queries to a combobox,
and run the merge code from there. Most of the stuff is covered in
www.mvps.org/access (the combobox fun) etc. Oh, and Albert Kallal has code
for mail merge on his website.... dig around here for "Super Easy Word Merge".
..

 

[Tom van Stiphout]

On Sat, 27 Jun 2009 20:46:38 -0700, "Doug Sanders"

You can use OpenDatabase to create a reference to that database, and
then iterate over the Tabledefs collection.

-Tom.
Microsoft Access MVP

 

[Douglas J. Steele]

Or just use

SELECT [Name]
FROM [;Database=\\ServerName\ShareName\Folder\File.mdb].MSysObjects
WHERE [Type] IN (1, 4, 6)
AND Left([Name], 4) <> "Msys"
ORDER BY [Name]

(Actually, if that's where the tables actually exist, you can get away with
WHERE [Type] = 1)