Excel to Access

  • Thread starter Thread starter RyGuy
  • Start date Start date
R

RyGuy

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/index.php?d=envbadacexportdao

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--
 
RyGuy said:
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
Click to expand...

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?
Click to expand...

Try -
db.Execute "delete * from tblTFI", dbFailOnError
 
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--
 
Back
Top