c9k9h
asked on
Import a spreadsheet from Excel 2003 to Access 2003
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!
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!
Can you do a manual import of the same spreadsheet?
ASKER
I have to run to a meeting, but will check it out when I get back. Thanks!
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.
Can you check your DAO references?
ASKER
I checked the DAO references and the 'Microsoft DAO 3.6 Object Library' is selected.
ASKER
Oh, and yes, I can do a manual import and it works fine.
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\Me trics 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.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "PCS_Import", "\\yourserver\sharename\Me
It depends on how the drive N mapping was done but you'll have to use the share point name.
ASKER
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!
Run-time error ‘3011’:
The Microsoft Jet database engine could not find the object ‘C:\Documents and Settings\c9k9h\My Documents\cha1dfs1:\cs\mis
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.
what raymond said....From your description of drive N, it sounds like maybe:
\\cha1dfs1\Data\cs\mis\Met rics Database\Commission Services Database.xls
But go ahead and use explorer to find the right path starting with the server name
\\cha1dfs1\Data\cs\mis\Met
But go ahead and use explorer to find the right path starting with the server name
ASKER
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!
Run-time error ‘2220’:
Microsoft Office Access can’t open the file ‘\\cha1dfs1\cs\mis\Metrics
At least it can find it now!
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Monarch project? what's that? Have you tried just linking the spreadsheet to Access?
ASKER
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!
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!
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.
ASKER
Thanks, Arji!