Importing Excel 2003 to Access 2003

[Grubsy4u]

Hi all,
My name is Daniel
Iam trying to learn visual basic and I have been having problems building a marco in Access 2003 that will allow me to extract data from my Excel 2003 spreadsheet into my Access table (fields have the same name, i just need to change the data). The data in the spreadsheet is huge with 32 columns and about 7000 rows. I have tried to wirte code but i keep getting error messages.
Maybe i need to start with something with smaller data.

This is what i have done so far. Does anyone know how to help?


Sub DAOFromExcelToA ccess()
' 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 :\FolderName\Da taBaseName.mdb" )
' open the database
Set rs = db.OpenRecordse t("TableName" , 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("FieldN ame1") = Range("A" & r).Value
.Fields("FieldN ame2") = Range("B" & r).Value
.Fields("FieldN ameN") = 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


I would appriciate any help or suggestion
Many Thanks

Regards Daniel

[VBPhilly]

Hi all,
My name is Daniel
Iam trying to learn visual basic and I have been having problems building a marco in Access 2003 that will allow me to extract data from my Excel 2003 spreadsheet into my Access table (fields have the same name, i just need to change the data). The data in the spreadsheet is huge with 32 columns and about 7000 rows. I have tried to wirte code but i keep getting error messages.
Maybe i need to start with something with smaller data.

This is what i have done so far. Does anyone know how to help?


Sub DAOFromExcelToA ccess()
' 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 :\FolderName\Da taBaseName.mdb" )
' open the database
Set rs = db.OpenRecordse t("TableName" , 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("FieldN ame1") = Range("A" & r).Value
.Fields("FieldN ame2") = Range("B" & r).Value
.Fields("FieldN ameN") = 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


I would appriciate any help or suggestion
Many Thanks

Regards Daniel

Hello Daniel,
what error are you getting and on what code/line is it erroring at?
Your code looks ok to me, so far. Just need more detail.

[Grubsy4u]

Well basically when i Press F8 to step through the code i get error signs after the first bit of code.

I'm not sure how to correct it tho?

What should i do?

[VBPhilly]

Well basically when i Press F8 to step through the code i get error signs after the first bit of code.

I'm not sure how to correct it tho?

What should i do?

an error could be many things. usually when you get an error, a messagebox comes up saying what the error was.
also, note the line of code the error 'breaks' on.
post what you find.

[codeCruncher]

Hi Daniel! Im having the same problem with my code. Have u tried adding the reference?

[Grubsy4u]

Many thanks...I have tried adding the reference yet?

I guessing i have made errors cause the data i am trying to import is very huge