MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1480

I peferred to use the ole db thru Microsoft access adp (project) because you do not have to go each PC to created that ODBC connection or changed the server.  I thinked that ODBC is older way of doing thing. The adp of Microsoft Access is cleaner, faster, and do not have go to each PC.

Another great and accurate article. A question I have is in your article you stated to select the 'primary key' for the tables or 'cancel' if you don't know them; if you don't know them and later find them out how can you update the table to use the 'primary key'? Would you have to recreate the linked table or is there a method that can be used to just update the linked table?

 Thanks for the article and information; this is spot on especially in cases of using Access 97 on the front-end (where ADP isn't an option).

 Thanks,
James R.

ADP started in Access 2000.

James, you touch upon something I'll be covering in a tip at the end of this month, but there is a Linked Table Manager plugin within Microsoft Access you can use to update the linked tables without destroying them.  Since you asked, I'll make a point of using your specific situation in the tip as an example.

 Thanks for the feedback James.

James, the new tip addressing your question has been finished and should go out in the next week.

Hi,

Our shop has used this extensively for a number of years, i.e., linking to SQL tables from MS-Access. I have recently noticed a problem and I am not sure how long it existed and/or if there is a fix.  Our environment is Windows XP(SP2), Office 2007/MS-Access 2007, SQL 2005 and SQL 2000. The error occurs when linked to either SQL 2005 or 2000.

 We have many large tables, 40,000+ records, and most have datetime fields, some with multiple datetime fields and these are the problem.When I create a new linked table I choose the External Data tab in the MS-Access "ribbon" at the top, I choose "More" under Import and go through the steps to link a table. After completing the link, if I open the table, still within the "External Data" ribbon tab, the table opens and I can focus on any cell, use arrows or tab to move quickly from field to field NO PROBLEM. However, if I select the "Home" tab from the top ribbon, the linked table remains open and I can focus into any cell and move using arrow keys or tabs, BUT, if I move into a datetime field, there is a long delay(30 secs - 5 minutes) before control returns. When it does, the total record count now appears in the bottom status bar("1 of 40,000" for example). If I move down the same datetime column there is no more delay. However, if I move into another datetime column, the delay recurs. It seems like the first time each datetime column receives focus it loops through each record doing something and it takes way longer than simply clicking the "Last Record" arrow in the bottom status bar.

 If I close the table and open again the same problem occurs where the initial focus to a datetime column results in long delays. This only occurs when the current ribbon tab is "Home". Pretty weird, huh?

I would appreciate it greatly if you can solve this problem but, whether you can or not, would you recommend another good forum for this type of SQL/Access question. Mostly I deal with ASP.Net stuff.

Thanks,

Pat Grealy

I suspect that this is because Access will return all the values of the table into memory and needs to do conversion for each row in the table to get it from the SQL datetime format to the default date format for Access.  When it does this you'll get moved back to the first record.  Once you switch fields, it reconverts once again, pulling all the data into Access yet again (another delay.)

This is purely conjecture though, but makes sense with the behavior you're experiencing.

I am just curious...

I am using ADOX to create Linked Tables from a SQL 2005 database.  By default the ADOX Table object does not contain the owner (in your example dbo_).  I have a need to INCLUDE the owner in some cases.  Do you know of a way to make this happen using ADOX?

Pat, i am running into a similar situation, were you able to find any acceptable work arounds to this issue.

Thanks,

No, I never found a solution. The work-around that I use is to stay in the “External Data” tab of the Access 2007 ribbon if possible, rather than returning to the “Home” tab. – Pat G.

I was able to obtain the owner by using the ODBC Driver {SQL Native Client}

I am wanting to link an SQL Server Database query view as a table in an Access database.  My question is, will it act like a regular Access table that can be shared or like an Excel linked table, which I've had problems sharing when multiple users are doing reports that are based on queries using the same tables.

My hope is that by linking it directly to the SQL database I can solve that problem.  Do you happen to know if that is the case?

PS - as you can tell, I'm new at the SQL thing, we've just been using the CSV files output by the database and I'm on the user side, not using direct queries on the database.  Thanks for any insight.

The articles and tips have been helpful, I'm going to keep searching.  Thanks!

 I'm curious how this may work programmatically? Is it possible to implement this linking technique and access a .mdb file using ADO in C++ and all the SQL Server linking happens transparently? I'm just guessing that this is probably something handled on the frontend in Access and wouldn't be transparent on a programming level when communicating directly with the MDB file. Thanks for any insight!

Angelgirl -  You can share the database - but doing so will require a DSN on each machine.