Welcome To The Access Unlimited Newsletter - Edition 61   
See all newsletters

Access Unlimited is an email newsletter that provides free tips, help and information for skilled Microsoft Access users and related software disciplines.

Produced by Garry Robinson (known below as "Ed") from Sydney, Australia.

In this edition, 

Service Pack introduces Access to Excel Linked Table Problems
Access Workgroup files and shortcuts
New Database Repair service at vb123.com
Good reading links
 

< GUACAMOLE DIPPED -ACCESS TO EXCEL LINKED TABLE GOTCHA >

In the latest service pack for Access 2003 and also in an automatic upgrade for Access 2002 lurks a significant "Gotcha" for those people who happen to manage Excel data (cells) through Access linked tables. The change to Access is simple, before the upgrade you could change Excel data through the linked tables and now you only have a read-only view. Whilst this was always a fairly unstable linked table option, if you used it sparingly, it could do the job. Well, now if your database uses this feature for managing data, you are in a spot of bother.  More on this issue in kb 904953 at http://support.microsoft.com/kb/904953/

Now it is more than likely that this software modification is related to the following successful patent claim that is discussed at the following website

http://news.com.com/    also at http://news.zdnet.com/2100-9593_22-5735432.html

If you are interested in what this means, have a look at the article that I wrote for Smart Access back in 1998 that describes using Excel as a backend database.  Granted, I have rarely used the technology since, but when this upgrade popped up in my Windows Upgrade an hour or so ago, I was very curious.  So I tested the download with the article before and after the upgrade and guess what, the links went from read/write to read-only.

http://www.vb123.com/Toolbox/98docs/excelbe.htm

Note that the following Remote Database query also reverts to read-only mode.

SELECT [Companies$].*, * FROM [Companies$] IN 'c:\tmp\excel_be.xls '[Excel 8.0;];

where Companies is the name of the worksheet in Excel.

My concern on this issue is that quite a lot of applications may fail for no apparent reason. "In my humble opinion, Microsoft should release a tool so that we can search all our Access databases for the use of Excel linked tables."

Checking If You Have A Problem

Run the following query in your front-end database(s)

SELECT Name FROM MSysObjects WHERE Connect Like "*Excel*"

Also search your VBA for the term 
[Excel

if you happened to be one of the few people on the planet who used Excel with remote queries.

Remember this is not a problem if you are only reading Excel and it may actually stabilize a few Excel spreadsheets because people won't be able to accidentally change the data. Also in case you were worried, Excel Automation is not affected by this change.

< Related Word / Access Upgrade Issue >

Posted from Simon, a reader of the newsletter

Keep up the good work, your articles are very useful to us semi pro developers :-)

Re todays newsletter detailing latest office upgrade breaking excel links.... A recent Office upgrade recently also damaged my automated mail merges.

I inherited a system which produces where I work...these are merged into a word document based on XXXX type. Its been working well for years, and suddenly a couple of weeks ago one user started getting "requested object not available" errors during the merge. She ran the process on her colleagues machine and it worked without an error. The next day the colleagues machine displayed the same error.

So I went over, tried it on a third machine ( a member of staff who had been off for 2 weeks), and it worked ok as normal.... Then I remembered our windows upgrade server only delivers one package per day....so each day the pc was getting upgraded a little more, and eventually one service pack was arriving which was breaking the mail merge.  Copious searching and debugging revealed the problem to be here:

With .ActiveDocument.MailMerge
  .Destination = wdSendToNewDocument
  .SuppressBlankLines = True
  With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
  End With
  .Execute Pause:=False
End With

and the fix to be this:

With .ActiveDocument.MailMerge
  .OpenDataSource Name:="c:\path to mdb", _
  LinkToSource:=True, AddToRecentFiles:=False, _
  Connection:="table certaward", _
  SQLStatement:="SELECT * FROM `table or query`"
  .Destination = wdSendToNewDocument
  .SuppressBlankLines = True
  With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
  End With
  .Execute Pause:=False
End With

I have experts exchange to thank for the solution....from this thread:

http://www.experts-exchange.com/Applications/MS_Office/Q_20629652.html

I don't know why this change occurred, or why adding the opendatasource property works but it does, and this info may be of use to one of your other readers.

Anyway hope you find this info useful. keep up the good work.  ... Simon
 

< NEW ACCESS SECURITY ARTICLE AT vb123.Com >
 
One of the readers of my book posted a good question on how to manage shortcut files with Access security workgroup files. Here is the full answer.

http://www.vb123.com/Toolbox/05_access/workgroupfiles_shortcuts.htm

< NEW ACCESS DATABASE RECOVERY SERVICE AT vb123.Com >
 
Every now and again our customers and even members of my team run into an Access database that is corrupt. Our usual approach is to stubbornly try and fix the database ourselves and generally we are good at doing this. Early in May, 2005, when we were battling with a database, I concluded that we were wasting too much time trying to fix our databases. After a lot of research, we teamed up with one the best repair companies on the web to bring you an automated repair service for your Access database. Please bookmark the following page for when you need your database repaired.

http://www.vb123.com/fixaccess/

< GOOD READING >