We help IT Professionals succeed at work.

Import a spreadsheet from Excel 2003 to Access 2003

c9k9h
c9k9h asked
on
468 Views
Last Modified: 2008-03-10
I found some help on this by doing a search before sending in this question.  It seems pretty straightforward, however, I'm getting an error message when I attempt to execute.  Here is the docmd line that I have in the 'On Click' event of a command button on a form:

Private Sub cmdImportPCS_Click()
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "PCS_Import", "N:\cs\mis\Metrics Database\Commission Services Database.xls", True, "A:F"
End Sub

Here is the error message: 'Run-time error 3275':  Unexpected error from external database driver (24).  I also found something on this that nico posted, but the link is no longer working.

Can an expert help me resolve this?  Thanks!
Comment
Watch Question

Commented:
Can you do a manual import of the same spreadsheet?

Author

Commented:
I have to run to a meeting, but will check it out when I get back.  Thanks!

Commented:
Have you considered just linking the spreadsheet to Access?  You can then work with it like you would any other table.  
I tried it and I can’t break it using 2003 or 2002.

Commented:
Can you check your DAO references?

Author

Commented:
I checked the DAO references and the 'Microsoft DAO 3.6 Object Library' is selected.  

Author

Commented:
Oh, and yes, I can do a manual import and it works fine.  

Commented:
If you don't want to link the sheet then try using an UNC path instead of a Drive mapping:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "PCS_Import", "\\yourserver\sharename\Metrics Database\Commission Services Database.xls", True, "A:F"

It depends on how the drive N mapping was done but you'll have to use the share point name.

Author

Commented:
This is what my 'n' drive is:  data on 'Chattanooga DFS server (cha1dfs1)'.  I've tried several combination of how to put it in code and I keep getting the same message (with the exception of whatever it is I enter in the 'cha1dfs1' area):

Run-time error ‘3011’:
The Microsoft Jet database engine could not find the object ‘C:\Documents and Settings\c9k9h\My Documents\cha1dfs1:\cs\mis ……….  Make sure the object exists and that you spell its name and the path name correctly.

And I thought I was missing something simple!  
start with \\cha1dfs1\ in windows explorer and see if it finds the server if you only have permisions to a share you may need the full path "\\cha1dfs1:\cs\mis ………. " The path to the file in explorer NOT using "N:\" is what you want.

Commented:
what raymond said....From your description of drive N, it sounds like maybe:

\\cha1dfs1\Data\cs\mis\Metrics Database\Commission Services Database.xls

But go ahead and use explorer to find the right path starting with the server name

Author

Commented:
I seem to be getting closer, and at this point I think I need to get some internal help to find out what's going on.  Like one of you mentioned, it may be a 'read-only' thing.  I work on the file and update it, so I'm not sure what's up.  Here's the message I get now:

Run-time error ‘2220’:
Microsoft Office Access can’t open the file ‘\\cha1dfs1\cs\mis\Metrics Database\Commission Services Database.xls’.

At least it can find it now!

Commented:
It does sound like you are on the right track.  I agree that you should get your IT guys on this issue.  It may be a file permissions issue as well.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I've decided to use a Monarch project to handle what I need, and a batch file will load the data into Access.  Interestingly, the prj file has no problem opening and reading the file on the N drive.  Must be the drivers or something.  Anyhow, I split the points between RaymondSteers and Arji since you both helped me to try and resolve.  I appreciate it!  Thanks!

Commented:
Monarch project?  what's that?  Have you tried just linking the spreadsheet to Access?

Author

Commented:
Monarch is pretty cool. (www.datawatch.com - Monarch V8 is in the spotlight section.) It takes reports and puts data into columns and rows.

I don't want to link because the data in the 40+ excel spreadsheets will change each month and the new data has to be appended to the existing data in Access.  Hmmmm, although that will be a problem in Monarch also, since an append is not available though code in the version I have.  You have given me something to think about.  I agree it is better to use as few applications as possible.  Hmmmm.  Thanks!

Commented:
Appending data from a linked spreadsheet is pretty much the same as doing it from a linked table.  You just run and Append query first and then a Delete query to delete the appended data from the spreadsheet to get it ready for the next batch.  Make backups of your tables and spreadsheets and give it a try.  It might make your life a little simpler.  You just want to be sure your data types match correctly from  your spreadsheet to your table.

Author

Commented:
Thanks, Arji!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.