In Access how do I change data type in importing field from Excel

[AlamBique]

Hi there

I'm using Access 2007 to import data from Excel using VBA. I have this code:

Private Sub cmd1Importa_Click()
Dim myRec As DAO.Recordset
Dim strQry As String
Dim dbExcel As DAO.Database
Dim rsExcel As DAO.Recordset

Set myRec = CurrentDb.OpenRecordset("Tabela1")

Set dbExcel = OpenDatabase("C:\BasesTestes\Testes\TabelaParaImpo rtar.xlsx", False, True, "Excel 12.0; IMEX=1;")


Set rsExcel = dbExcel.OpenRecordset("Plan1$")

Do While Not rsExcel.EOF
myRec.AddNew
myRec.Fields("Coisa1") = rsExcel.Fields("Coisa1")
myRec.Fields("Coisa2") = rsExcel.Fields("Coisa2")
myRec.Fields("Coisa3") = rsExcel.Fields("Coisa3")
myRec.Update

rsExcel.MoveNext
Loop
End Sub

But the ODBC driver reads only the first 16 records and chose for the column 3 (Coisa3) data type String with 255 characters max. And in the worksheet that I have texts with over 255 characters that are not being imported. I can choose the data type of the third column as memo? How?

Thanks

[June7]

Strings of more than 255 are problematic. Is this a one-time effort or will have to be done regularly? Did you try the import wizard? The wizard has a memo field type designation.

This code seemed to help another poster with same issue.
http://www.accessmvp.com/kdsnell/EXC...m#WriteFileRst

[AlamBique]

I can't use wizard. I use the VBA becawse this is a distributed system, operated by several people who do not understand anything dde data bank. They just have to push a button and go.
O que eu preciso é de uma maneira de definir o campo por VBA como é feito no wizard.

[June7]

Opening a recordset of Excel range may be like setting a link. I read one article that stated linking truncates fields greater than 255 characters. The recommended solution was to import to a table then the field would not be truncated. Another article stated that the import wizard or DoCmd.TransferSpreadsheet reads first 25 records to determine field type.

Do this. Use wizard or DoCmd to test full import to a new table. Does the data still truncate? If not, then test APPEND sql of these records to the target table. If this gets data in correctly, solution might be import then append and delete the import table.

[ssanfu]

I found this site that has lots of info on Access/Excel.


Ken's Examples for Importing from EXCEL Workbook Files

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

[June7]

Nice link. At the very end of page, find:


The truncated text string that you see is because Jet (ACCESS) sees only "short text" (text strings no longer than 255 characters) values in the first 8 - 25 rows of data in the EXCEL sheet, even though you have longer text farther down the rows. What ACCESS and Jet are doing is assuming that the "text" data actually are Text data type, not Memo data type. One of these suggestions should fix the problem:

1) Insert a dummy row of data as the first row, where the dummy row contains a text string longer than 255 characters in the cell in that column -- that should let Jet (ACCESS) treat that column's values as memo and not text.

2) Create a blank table into which you will import the spreadsheet's data. For the field that will receive the "memo" data, make its data type "Memo". Jet (ACCESS) then will "honor" the field's datatype when it does the import.

It's possible to force Jet to scan all the rows and not guess the data type based on just the first few rows. See this article for information about the registry key (see TypeGuessRows and MaxScanRows information): http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/ [ NOTE: There are some reports by others that this registry key may not work as expected when using Windows XP SP3 or when using ACCESS 2007. ]