Importing Data from Excel

[Dustin Ventin]

I have a polished Access application that I'm trying to upsize to using a SQL
Server back-end. Obviously I have some things to work through, and one of
them seems to be the automation required for importing Excel spreadsheets
into my tables. Orginally I was using the following code:

DoCmd.TransferSpreadsheet acImport, TableName:="tblMyTable",
FileName:=strImportPath, HasFieldNames:=True

Now, when I was using Access tables this code worked flawlessly. Now, as
soon as this line is reached, Access throws the dreaded Unidentified Error
screen and crashes unceremoniously.

I've been looking all over the Internet, but nobody seems to have a very
specific idea of how to fix it. Any help would be great appreciated.

Dustin

[Jeff Boyce]

Dustin

I'm not completely clear from your description whether you are using the
SQL-Server tablename in place of "tblMyTable".

Also, are the permissions on the SQL-Server database such that you are
allowed to create a new table?

Instead, what about the idea of flushing out the records from an "import"
table in SQL-Server, then loading (appending) the new records from the
spreadsheet?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Post by Dustin Ventin
I have a polished Access application that I'm trying to upsize to using a SQL
Server back-end. Obviously I have some things to work through, and one of
them seems to be the automation required for importing Excel spreadsheets
DoCmd.TransferSpreadsheet acImport, TableName:="tblMyTable",
FileName:=strImportPath, HasFieldNames:=True
Now, when I was using Access tables this code worked flawlessly. Now, as
soon as this line is reached, Access throws the dreaded Unidentified Error
screen and crashes unceremoniously.
I've been looking all over the Internet, but nobody seems to have a very
specific idea of how to fix it. Any help would be great appreciated.
Dustin

[Dustin Ventin]

Yes, I'm using the correct tablename. :D

I believe I do have the permission to create new tables in the SQL Server
database, because I'm using the adminstrator log-in.

I like the idea you have of dumping all the old records and appending the
new records from Excel. I have quite a bit of experience in writing Excel
automation coding, but sometimes I notice that tends to run a little slow.
The Excel files that will be appending will many times be literally thousands
of records long. Just so I'm aware, were you suggesting the use of Excel
automation VBA, or do you know of a better way?

Thank you very much for your assistence,

Dustin

Post by Dustin Ventin
Dustin
I'm not completely clear from your description whether you are using the
SQL-Server tablename in place of "tblMyTable".
Also, are the permissions on the SQL-Server database such that you are
allowed to create a new table?
Instead, what about the idea of flushing out the records from an "import"
table in SQL-Server, then loading (appending) the new records from the
spreadsheet?
Regards
Jeff Boyce
Microsoft Office/Access MVP

Post by Dustin Ventin
I have a polished Access application that I'm trying to upsize to using a SQL
Server back-end. Obviously I have some things to work through, and one of
them seems to be the automation required for importing Excel spreadsheets
DoCmd.TransferSpreadsheet acImport, TableName:="tblMyTable",
FileName:=strImportPath, HasFieldNames:=True
Now, when I was using Access tables this code worked flawlessly. Now, as
soon as this line is reached, Access throws the dreaded Unidentified Error
screen and crashes unceremoniously.
I've been looking all over the Internet, but nobody seems to have a very
specific idea of how to fix it. Any help would be great appreciated.
Dustin

[Jeff Boyce]

Dustin

I don't have sufficient experience with Excel automation to advise whether
Access automation or Excel automation would be preferable.

If this were mine, and I were working from within Access, I'd try using
Access features first (but that's the hammer I know).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Post by Dustin Ventin
Yes, I'm using the correct tablename. :D
I believe I do have the permission to create new tables in the SQL Server
database, because I'm using the adminstrator log-in.
I like the idea you have of dumping all the old records and appending the
new records from Excel. I have quite a bit of experience in writing Excel
automation coding, but sometimes I notice that tends to run a little slow.
The Excel files that will be appending will many times be literally thousands
of records long. Just so I'm aware, were you suggesting the use of Excel
automation VBA, or do you know of a better way?
Thank you very much for your assistence,
Dustin

Post by Dustin Ventin
Dustin
I'm not completely clear from your description whether you are using the
SQL-Server tablename in place of "tblMyTable".
Also, are the permissions on the SQL-Server database such that you are
allowed to create a new table?
Instead, what about the idea of flushing out the records from an "import"
table in SQL-Server, then loading (appending) the new records from the
spreadsheet?
Regards
Jeff Boyce
Microsoft Office/Access MVP

Post by Dustin Ventin
I have a polished Access application that I'm trying to upsize to using
a
SQL
Server back-end. Obviously I have some things to work through, and one of
them seems to be the automation required for importing Excel spreadsheets
DoCmd.TransferSpreadsheet acImport, TableName:="tblMyTable",
FileName:=strImportPath, HasFieldNames:=True
Now, when I was using Access tables this code worked flawlessly. Now, as
soon as this line is reached, Access throws the dreaded Unidentified Error
screen and crashes unceremoniously.
I've been looking all over the Internet, but nobody seems to have a very
specific idea of how to fix it. Any help would be great appreciated.
Dustin