Access TransferSpreadsheet problems

[braktoon]

i am writing an application in access and have to import a lot of data from excel. right now i am using the transferspreadsheet method to import the data to a table, but when i open the table there are fewer records than there are when i open the spreadsheet in excel. the only thing i can think of is that access is not importing records with blank fields. i am importing data with 10 fields, somewhere on the order of 45,000 records at a time. there are a few (some thousands) of records with a blank field here or there. i need my data to be accurate and this is disturbing me, any suggestions on how to remedy this problem?

thanks,
justin

 

[rss01]

Justin,

Instead of using the TransferSpreadsheet, could you just link the excel spreadsheet then run your query off the linked sheet?

 

[braktoon]

thanks for the suggestion, that is one of the solutions i am looking into. it should work, but does anyone know if working with a linked object will slow it down because efficiency is a major issue with this program.

 

[braktoon]

also...with a linked object how do i query the excel spreadsheet? is that even possible, i searched high and low and haven't found anything on running a query on a linked excel object. maybe i'm looking in the wrong places, but a few questions come up. for example, what would i use as the fieldnames? if anyone has done this before i would appreciate the help.

 

[mmaginniss]

I believe you can create a new linked table in access and link it to your excel spreadsheet. Then you can run a query off the linked table.

 

[braktoon]

good call, i am looking into this and will let you know how it goes. finding the code to create a new table that is linked to an excel spreadsheet is a little hard though, so if anyone knows off the top of their head i would appreciate it if you could share it.

 

[braktoon]

ok, this is what i have:
Set tdef = db.CreateTableDef("NewShipData2&quot
tdef.Connect = "Excel 97;Datebase=" & strFile
tdef.SourceTableName = Mid(strFile, intx + 1, Len(strFile) - 4 - intx)
db.TableDefs.Append tdef

i get a "Couldn't find installable ISAM" error, i have tried reinstalling access, but it doesn't do anyting different. any suggestions?

 

[rss01]

Did you get this working? I was off site for a couple of days and didnt get to check back.

Linking a excel spreadsheet makes the excel spreadsheet a table. You can then use that table name in any of your queries. One thing you cant do is update any of the data in the linked spreadsheet. You have to do this is excel.

 

[braktoon]

yeah, i ended up doing this another way, everyone here uses office 97 and has JET 3.51, so a few of the features didnt work. i got access to the database and queried it directly to create the sums that i needed, much easier.

thanks,
justin