Link SQL tables

Question

Link SQL tables

dUros

SSCommitted

Points: 1506
More actions
February 22, 2005 at 11:14 am

#65660

HI,
I have one question about link SQL tables in access mdb (2003). I know for three metods of link tables: DAO, ADO and docmd.TransferDatabase.
Which type of link is better?
What is difference between?
Which metod is recommendation?
If anybody know for some white papers on this metods....
I read about this metods (msdn, help,...) but nowhere about all three metods together - confront.
Thanks ...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jfmccabe SSCrazy Points: 2547 More actions · Answer 1

No idea about TransferDatabase method.

The standard view is that DAO is optimised for Jet (i.e. Access) databases and ADO has the features to cope with just about anything else. ADO can cope with querying Active Directory or any LDAP structure for example. ADO is reckoned to be slower then DAO when working with Access tables and faster than DAO whne working with other things. Not all the features of DAO are implemented in ADO/ADOX especially some of the more interesting code for schema searching.

Try a quick google on DAO and ADO to get a feel for this.

Pace SSC Rookie Points: 29 More actions · Answer 2

I am not entirely sure I can make out what you want to do.

You want to import a sql table into an access application ?

dUros SSCommitted Points: 1506 More actions · Answer 3

I understand ADO and DAO for using to manipulate with data. I use ADO for runing stored procedure on SQL server ....... DAO is good for manipulating with access data localy (access tables). But in some cases I nead linked table from SQL server and here is the question. You can link SQL tables with metod I refer to. Result is always the same - tables is linked. But what happend on background? Is the link (result) the same no matter which metod is used? Maybe some link working faster?

About docmd.transferdatabase command.

Is wery useful (simple) for import or export data from/to different sources.

For linking table I use:

strConnectionString=ODBC;Driver={SQL Server};Server=myServerName;Database=myDatabaseName;UID=myUser;PWD=myUserPassword

DoCmd.TransferDatabase acLink, "ODBC Databases" , strConnectionString, acTable, TableName, TableName

Pace,

I just want to link table in access mdb.

jfmccabe SSCrazy Points: 2547 More actions · Answer 4

Two basic points.

When you link (not in code) you get asked what are the key fields. It is very tricky to do this in ADO so I tend to use DAO to add the table to the tabledefs in the current (Access) database. I can then run some SQL in Access on Access to add an index (primary of course) so that the table can then be updated under the normal conditions. You will find with a normal link that sometimes tables are not directly updatable. This applies especially to views where Access has a terrible job sorting things out.

Next, when you link normally, you get a dbo.TableName format. When you link via tabledefs, you can call the table what you like.

On transferdatabase, it does what it says, copies the database so you get a snapshot not a link to live data.

dUros SSCommitted Points: 1506 More actions · Answer 5

I define key in table on SQL site and avoid RO link tables and asking for URI (unique record identifier), to avoid dbo.TableName must specify link table name (5th parameter in TransferDatabase).

Result of Docmd.TransferDatabase acLink,....... is the same as DAO and ADO. Data is live, not snapshot. I try all three metods and result is always the same - table is linked RW (if URI exists). But how link work in background, when I read or update the data in linked table? I don't know, maybe is link made with DAO (via tabledefs) more efficient, faster then ADO or transferDatabase.....???

jfmccabe SSCrazy Points: 2547 More actions · Answer 6

Finally had a look at TransferDatabase. Need to check on some more complex databases to see if it behaves.

As to background, all three technologies ultimately rest on ODBC connections. ADO uses a slightly different connection string but it's still ODBC in the background.

DAO is "optimised" for Access although it will work with other ODBC sources. ADO exposes a richer set of functions but has a weaker Access driver. So the general suggestion is to use ADO where possible for non-Access work and DAO when working with Access. However, ADO does not have such good facilities for schema manipulation as DAO mainly because it is difficult to write generic procedures for any database system. DAO and tabledefs is aimed at Access.

I tend to use DAO to link tables and ADO to run stored procedures.

dUros SSCommitted Points: 1506 More actions · Answer 7

I agree with you.

I will use DAO to link tables like you, and ADO to run stored procedure.

Is much better (performance) to execute data processing on SQL server and receive just result of data processing - with ADO.

In same rare cases I need link and in this cases I use DAO an Domain functions (DLookup,....). If you need small portion data from table (one or two fields from one record), Dlookup on link table is simple and faster then any other metod but must be careful on WHERE clause.

Most often I use link table for recordset on forms, becouse I use mdb not adp.

Thanks