Import Excel data to access using VB Script

[skancha]

I need to Export an excel sheet to an access table. I get one new excel sheet every month. I could able to do this with the below script.

Option Explicit

Code:

Dim objADOXDatabase,objExcelConn,objAccessConn,objAccessRS,objExcelRS,strSQL
Set objADOXDatabase = CreateObject("ADOX.Catalog")

'Create the database file
objADOXDatabase.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=w:\customer.mdb"

Set objADOXDatabase=Nothing

Set objExcelConn = CreateObject("ADODB.Connection")
Set objAccessConn = CreateObject("ADODB.Connection")

objAccessConn.open  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=W:\customer.mdb"
'objExcelConn.Open objAccessConn
  
objExcelConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objExcelConn.Properties("Extended Properties").Value = "Excel 8.0"
objExcelConn.Open "W:\test.xls"
  
set objAccessRS = Createobject("ADODB.Recordset")
Set objExcelRS = CreateObject("ADODB.Recordset")

strSQL = "SELECT * INTO [Scenario] FROM [Excel 8.0;DATABASE=W:\test.xls;HDR=No;IMEX=1].[sheet1$];"
objAccessConn.Execute strSQL

But when i am executing this script i am getting an error in the last line "Record is too large". My excel sheet is containing around 255 columns and 2100 rows.

Can anyone tell me how to resolve this error and successfully import excel sheet to access table?

Any help is appreciated!

Thanks
Sheela

[Saket]

Not sure,I think, this could be a data issue.
When you import tables from Excel the field length defaults to 255 characters (or 256 bytes). May be with your fields it is going over the limit.
Your need to normalize your data. analyze the data and create multiple tables. Then link to portions of the spreadsheet and append the data correctly to the tables.

[skancha]

I can not split this into multiple table. I want this in one single table. Is there any way to import excel data as Memo field in access using VB script.

Thanks
Sheela

[Saket]

May be, I am not an expert in access but as i can understand if there are too many fields and it goes over the limit then it wont be possible.
you will have to use multiple tables.