MS Word macro cannot open Access database file

  Nosmas 21:53 15 Nov 04
Locked

A few years ago I designed and implemented a database for recording details of the membership of my bowls club. Originally the club system was running W98SE and Office 97, and no problems were experienced when the Office product was upgraded to Office 2000.

Very recently a completely new system has been purchased and is running XP and Office 2003. About two years ago I wrote some Word macros to automate a monthly routine that extracts information from the database, and then runs a Mail Merge to insert that data into a Main Document. The suppliers installed the new system before I was able to make copies of the macros held in the Word 97 'normal.dot' template. Fortunately I have copies of the macros on my home PC (W98 and Office 97) and have copied them to a floppy and pasted them into the Word application in Office 2003.

To ensure that the club's lady administrator should not experience any problems when running the macros I stepped through each of them. To my dismay two of the macros failed at exactly the same point, and I received an error message - "This operation cannot be completed because of dialog or database engine failures. Please try again later." Clicking the OK button resulted in a further error message "Run-time error '5922'. Word was unable to open the data source." Clicking the Debug button then highlighted the offending lines of code, which are: -

ActiveDocument.MailMerge.OpenDataSource Name:=Source, LinkToSource:=True, _
Format:=wdOpenFormatAuto, Connection:= _
"TABLE SBCMembers", SQLStatement:= _
"SELECT * FROM SBCMembers", SQLStatement1:=""

(At this point the macro code had correctly opened the relevant Main Document, and the database file had been defined in earlier statements 'Path = "C:\xxx\xxx\" and 'Source = Path & "SBC Membership.mdb"')

The engineer who installed the new system has copied the entire contents of the old HDD into a folder on the new HDD. I am 99% sure that my copies of the macros were the same as those on the original club system, but if possible would like to check the latter in case there is something I altered on the club's old system but have since forgotten.

Please can anyone (a) suggest how I might be able to access the macros as written in the old system and/or (b) tell me if there are any known problems when transferring either the database file or the macros from the old OS and Office 2000 to XP and Office 2003, or suggest any other reason for the error?

  VoG II 22:11 15 Nov 04

There have been changes in both Word and Access VBA since Office 97. I suggest that you record a macro in Word to do what you want then view it in the Visual Basic Editor to see the current syntax.

  Nosmas 00:49 16 Nov 04

Thanks for your suggestion. Unfortunately these macros were not just straightforward recordings of keyboard actions. They started off as recordings but, because I ran into certain problems (cannot remember all the details now), I eventually resorted to viewing them in the VB Editor and (with a certain amount of help from this forum and elsewhere) modified them by writing direct VB code.

Although I have a reasonable grasp of the workings of VB, I am not an expert programmer. I would therefore welcome suggestions from amy more experienced members as to any errors they can spot in the syntax of the lines of code I have quoted, that may have caused the error condition.

I would particularly like to know if it is possible somehow to view the original macros which must surely be buried somewhere in the folder holding the contents of the old HDD. This is just to compare them with what I have copied from my own PC. If there are no differences, then it is very possible the error is caused by changes in the syntax.

  AccessMoron 10:19 16 Nov 04

I think it may be the way that you are accessing the database. In office 97, microsoft used jet 3.5 and DAO, not they use Jet4.0 and ADO.
Look at the references (in VBA, Tools References) and make sure that there is a reference to Jet.

  Sir Radfordin 10:42 16 Nov 04

If you have the full contents of the old hard drive available to you then you should be able to do a search for normal.dot within that file. Opening that should then allow you to see the macros saved within it.

I am inclined to agree with VoG and Access Moron that the problem could well be to changes that have been made within the way VBA works.

VoG's advice of re-recording the macros may be the best, especially since the mail merge functions has been improved in newer versions of Word.

  Nosmas 12:45 16 Nov 04

Thanks for your suggestion, but having looked on my own system (Office 97)in VBA > Tools References there are only five 'ticked' lines - VB for Applications, Microsoft Word 8.0 Object Library, OLE Automation, Microsoft Forms 2.0 Object Library, and Microsoft Office 8.0 Object Library.

The only mention of Jet is an unticked line 'Jet Expression Service Type Library'. Please can you enlighten me further re finding which Jet version is on my own system and the club's Office 2003 application? Also, to what does DAO and ADO refer?

  Nosmas 12:47 16 Nov 04

Thanks for your response. I tried this on my own system and when I clicked on 'normal.dot' it just opened Word with a blank Document1. If I do this on the club system, doesn't this mean that it will try to Launch the Word application from Office 2000, which could cause problems insofar as that application will have been linked to registry entries for the old OS (W98SE)? Might it have some effect on the registry for XP? I certainly don't want to cause any upsets on the brand new club system!

  Nosmas 18:20 16 Nov 04

Bump for the evening shift. Any other ideas/suggestions please - in particular my query re Sir Radfordin's idea to open the normal.dot from the old system.

Harking back to the development of these macros, a lot of the work was done on my system (Office 97) and then ported to the club system which was then running Office 2000, and I can't recall having any problems or having to amend the VB code. (But that is why I would like to check the original macros on the club system against those I have just copied from my own system - just in case!)

  Sir Radfordin 09:43 17 Nov 04

Having found a copy of normal.dot if you right click on it you should be able to open that template and not just create a new word document using it as a template.

Having done that you stand a better chance of getting the VBA out of it.

If you want to email a copy of the file for me to look at click on the yellow envelope and I'll reply this evening with an address for you.

  Nosmas 12:16 17 Nov 04

Thanks for your reply and kind offer. On my own system I right clicked on 'normal.dot' and selected Open but it still launched Word 97 and displayed a blank page, only this time the page was titled 'Normal.dot' instead of 'Document1'.

I now suspect that if I tried this on my club's system it would launch Word from Office 2003 and NOT Office 2000 Word. Providing this doesn't interfere in any way with the settings etc. on the club's new system it will not matter and I should then be able to view the original macros and compare them with what I have copied from my own system to the club's system. Do you agree?

Like you I am inclined to agree with VoG™ and AccessMoron, that the error has been caused by changes in the working / syntax of VB, but I would have thought MS would have publicised these and suggested workarounds. I have tried searching the MS Knowledge Base without success. I fear AccessMoron's references to Jet, ADO and DAO are a bit over my head. Do they mean anything to you? I am anxious to overcome this problem a.s.a.p. as the monthly run is now somewhat overdue.

  Sir Radfordin 13:00 17 Nov 04

I would agree with you when you say that opening the file not just creating a new document from the template will allow you to see the marcos in it.

Last time I had to add in extra references I went into Access and do Alt+F11 then from the VBA go to Tools/References and check what is ticked.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Method Studios' title sequence for BBC series Taboo is truly unsettling

Best Pages for iOS tips | How to use Pages for iPad & iPhone: 6 simple tips to get more out of…