problem linking XLS spreadsheet to access97 using vb6 
Author Message
 problem linking XLS spreadsheet to access97 using vb6

I am linking an excel spreadsheet into access 97 within VB 6.0 in the
following manner:

       Set db = DBEngine.Workspaces(0).OpenDatabase(UG_PCSDBPath) ' this
opens the existing access         database

         DoCmd.TransferSpreadsheet acLink, 8, "HireXLS", "spreadsheet.xls",
True 'links xls spreadsheet to the     database

Then I run an append query, "appendhire" to append the linked spreadsheet
(linked in table HireXLS) to an existing Access table:

     Set qdf = db.QueryDefs("AppendHire")

Finally I delete the linked table

     If IsAnObject("HireXLS") Then
        db.Execute "Drop Table hirexls"
     End If

the only way I can get this to work is if the user opens Access 97 and the
exisitng database prior to running the
  "doCmd.transfer...." statement
and closes the database and Access after running the statement.

If they do not, if for example, they just minimize the Access screen and
database after opening them, they will get various unusual error messages,
for example that the Hirexls table does not exist.

Is there any way to nicely avoid these errors or do I have to have them
follow this awkward Open, Close routine

Thanks for any help.

SM Haig

Sun, 02 Mar 2003 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Problems using VBA to link or DAO to import Excel file into Access97

2. Problem using doCmd.transfertext with vb6 and access97

3. How to link a passwd protected Excel spreadsheet to Access in VB6

4. linking an excel spreadsheet to an access database from vb6

5. Add a row to a spreadsheet using a MS Access DDE-link

6. Using DAO to link a spreadsheet: ISAM error

7. linking an excel spreadsheet to an access database using vb

8. Reading from *.xls files into Access 97 Using VB6

9. VB6 and Access97 link tables

10. linking an excel spreadsheet to an access table using vb

11. Creating XLS (Excel) Spreadsheet

12. Creating XLS (Excel) Spreadsheet


Powered by phpBB® Forum Software