Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel to Access
I run the code below and get an error 'Data Type Conversion Error':
Sub DAOFromExcelToAccess() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim db As Database, rs As Recordset, r As Long Set db = OpenDatabase("C:\TFI.mdb") ' open the database Set rs = db.OpenRecordset("tblTFI", dbOpenTable) ' get all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("ID") = Range("A" & r).Value .Fields("PROJECT NAME") = Range("B" & r).Value .Fields("DATE REC") = Range("C" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing db.Close Set db = Nothing End Sub The error occurs on this line: ..Fields("ID") = Range("A" & r).Value I have the reference to DAO. The code is from this site: http://www.erlandsendata.no/english/...badacexportdao I can't determine what the issue is. I'm guessing the problem is in the Access table... Can someone figure out what may cause this issue? I would like to take the all data from one specific sheet, I guess it could be active sheet or the sheet name "TFI". Anyway, I want to send all records from this sheet to the Access table, named tblTFI. I am hoping to overwrite all data in that Access table. Right now there is data in that Access table (from last week). The table is not empty. m Is this causing the problem? What do I need to do to overwrite all data in the Access table with the current data from the Excel sheet? Regards, Ryan-- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel to Access
"RyGuy" wrote in message
... I run the code below and get an error 'Data Type Conversion Error': Sub DAOFromExcelToAccess() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim db As Database, rs As Recordset, r As Long Set db = OpenDatabase("C:\TFI.mdb") ' open the database Set rs = db.OpenRecordset("tblTFI", dbOpenTable) ' get all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("ID") = Range("A" & r).Value What datatype is tblTFI.ID, and what sort of data is in Range("A" & r)? Does this work? ..Fields("ID") = clng(Range("A" & r).Value) What do I need to do to overwrite all data in the Access table with the current data from the Excel sheet? Try - db.Execute "delete * from tblTFI", dbFailOnError -- roger |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel to Access
Thanks! I ended up using changing the Data Type in all of the Access fields
to Text. Text can accommodate up to 255 characters, and it seemed to be very flexible (in terms of what data types can flow into all the fields) too. Thanks again, Ryan-- -- RyGuy "roger" wrote: "RyGuy" wrote in message ... I run the code below and get an error 'Data Type Conversion Error': Sub DAOFromExcelToAccess() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim db As Database, rs As Recordset, r As Long Set db = OpenDatabase("C:\TFI.mdb") ' open the database Set rs = db.OpenRecordset("tblTFI", dbOpenTable) ' get all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("ID") = Range("A" & r).Value What datatype is tblTFI.ID, and what sort of data is in Range("A" & r)? Does this work? ..Fields("ID") = clng(Range("A" & r).Value) What do I need to do to overwrite all data in the Access table with the current data from the Excel sheet? Try - db.Execute "delete * from tblTFI", dbFailOnError -- roger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trust Access to Visual Basic Project - Access to Excel and back | Excel Programming | |||
How do I access the access data via Excel 2002 with auto update ? | Excel Programming | |||
Importing data from Access to Excel, but I need to vary the table from Access | Excel Programming | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
Getting Access Error Messages when running Access through Excel | Excel Programming |