Dynamically create linked tables via C#

[scott_js]

I need to dynamically create linked tables in Access via C#. Does
anyone have any idea on how to do this?

I was hoping to simply be able to execute some SQL to create the linked
table, but that that does not seem to be possible.

I did see someone suggest including the ADOX COM object, but I prefer
not including this COM object unless I really need to.

Can I create a maco which will create a linked table and then call the
macro from C#?

Can I insert records into the internal Access tables to create the
linked tables?

Any and all help is appreciated.

Thanks,
Scott

 

[Douglas J. Steele]

You really need something like ADOX or DAO in order to do it. If you try use
Automation to have Access do it, you're going to be using DAO (plus require
that Access be installed on the machine)

 

[david epsom dot com dot au]

If you are going to use a macro to do this, you
are better off writing the macro in cScript, rather
than writing the macro in Access macro language.

Writing the macro in Access, you will have to
shell to or create an Access object.

Writing the macro in VBS or cScript, you will
only have to shell or load a windows scripting
host, which is much more light weight than loading
all of Access.

However, I have to wonder about the need to dynamically
create linked tables.

Sometimes what people need to do is dynamically
create views. You can do that using SQL.

Remember that in a View, the database specification
is normally implicit, but it doesn't have to be:

"select * from database.table"

"update database.table set field = value where
database.table.field = 'fred'"


(david)

 

[scott_js]

Dave and Doug,
Thanks for the help and suggestion.

I'm already creating the view dynamically, but I was hoping to also be
able to create the linked tables dynamically too.

Doug, you mentioned that you can specify the name of the database. If
I'm trying to connect to a SQL server database, is there a way to
specify the name of the server? What about the username and password.
I figured I needed a linked table to do this.

Thanks,
Scott

 

[Douglas J Steele]

Actually, I didn't say anything about specifying the name of the database:
that was David. (unfortunately, since you trimmed everything of relevance
from your repost, I had to go back and search for the previous messages!)

When you're creating a linked table to SQL Server, you need to specify the
Connect property. To connect to SQL Server, you need to use ODBC (can't use
Ole DB). Usually, you use a DSN, so the connect property looks something
like:

ODBC;DATABASE=database;UID=user;PWD=password;DSN=datasourcename;

However, it's possible to go DSN-less by specifying the appropriate DSN-less
connection string.

From the point of view of VBA, I show how to do this at
http://www.accessmvp.com/djsteele/DSNLessLinks.html

Carl Prothman has a good site for DSN-less connection strings at
http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx

 

[Guest]

The 'name of a database' in Jet SQL is a Jet connect string.

SELECT * FROM
[ODBC;DSN=FRED;....].[TBL]
etc etc.

Jet has a number of ways of storing connections to external
data sources. Off the top of my head, these are

Linked Tables
PassThrough queries
Normal Queries/Views

The first two require connect information embedded
in the specification, and are easier to connect and
disconnect because of that.

Linked tables also store information about the table
structure, which is supposed to make dynamic SQL
more efficient.

PassThrough queries pass the SQL straight through
to the connection, which is supposed to be more
efficient. When connected to a Jet database, they
probably store the same kind of meta data as a
Linked Table.

Normal Saved Queries do store some kind of source
meta data, because they are saved in a compiled form,
but they are not commonly used, because for most
purposes storing the connect information separate from
the SQL is easier to maintain (improved cohesion, reduced
coupling). However, Normal Saved Queries (views)
can be created by CreateView DDL, which is not true
of PassThrough queries or Linked tables.

Jet supports two different SQL syntaxes, you can try
this yourself to see what works in your DDL.

(david)