VBA to upload Data from Excel to Access

[JoeRooney]

Hi,

Not sure if I should post this on the excel or access forum butthought I would try here first.

I have used the below code in the past to upload data to MSaccess 2010 from Excel 2010 , but I am now using MS 2013 and the code is nolonger working.

I am receiving the error unrecognised database format
"C:\Users\c0257435\Netherlands_034-DB.accdb"

Tome the file path looks fine and I cant figure out why it is failing on this bitof code.

Would anyone have any suggestions why?

Thanks,

Sub UploadToDB()

'to enable this macro to run you must enable the MS AccessDB references to do this follow the steps below: -
'Tools > References > check 'Microsoft DAO 3.6 ObjectLibrary' > OK

Dim Db As Database
Dim Rs As Recordset
Dim Path As String,TableName As String

Path ="C:\Users\c0257435\Netherlands_034-DB.accdb"
'Change the Pathstring to where you have the Database and do not use a Drive letter in the pathif possible
TableName ="SCF_File_Historical" 'Table Name that is in Access

Set Db =OpenDatabase(Path) ' Open Database

Dim appAccess AsObject


Set Rs =Db.OpenRecordset(TableName, dbOpenDynaset) 'Open Table RS

Sheets("SCF").Select
Range("A1").Select
Range(Selection,Selection.End(xlToRight)).Select
Range(Selection,Selection.End(xlDown)).Select

Do WhileActiveCell.Value <> ""

ActiveCell.Offset(0, -39).Select


With Rs 'Week 1Sat
.AddNew ' Add aNew Record to Access Table

Rs.Fields("POLICY_ID") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PRODUCT_TYPE") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("OPERATION_TYPE") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("POLICY_STATUS") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("OPERATION_DATE") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("INCEPTION_DATE") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("EXPIRATION_DATE") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("POLICY_TERM") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("END_REASON") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("CAPITAL_AMOUNT") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("SUM_INSURED") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("MONTHLY_INSTALMENT_AMOUNT") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PREMIUM_RECORDS") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("COMMISSION_POLICY") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("TAX_POLICY_LEVEL") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("NET_PREMIUM_POLICY") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("INTEREST_RATE") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_1_FIRSTNAME") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_1_LASTNAME") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_1_GENDER_LABEL") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_1_NATIONALID") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_1_BIRTHDATE") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_1_ADDRESS_LINE3STREETNAMEANDNUMBER")= ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_1_ADDRESS_ZIPCODE") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_1_ADDRESS_CITY") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_1_PHONE") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_1_EMAIL") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("LANGUAGE_1_CODE") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_2_FIRSTNAME") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_2_LASTNAME") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_2_GENDER_LABEL") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_2_NATIONALID") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_2_BIRTHDATE") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_2_ADDRESS_LINE3STREETNAMEANDNUMBER")= ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_2_ADDRESS_ZIPCODE") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_2_ADDRESS_CITY") =ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_2_PHONE") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("PERSONS_2_EMAIL") = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
Rs.Fields("LANGUAGE_2_CODE") = ActiveCell.Value


.Update 'Sends thedata to Access

End With

ActiveCell.Offset(1, -1).Select

Loop


'MsgBox "The information on the form has been uploadedto the forms database - Thank You."

End Sub

 

[=ODIN=]

Your macro says:

[COLOR=#333333][FONT=Calibri][SIZE=3][COLOR=#000000]'to enable this macro to run you must enable the MS AccessDB references to do this follow the steps below: -[/COLOR][/SIZE][/FONT][/COLOR]
[COLOR=#333333][FONT=Calibri][SIZE=3][COLOR=#000000]'Tools > References > check 'Microsoft DAO 3.6 ObjectLibrary' > OK[/COLOR][/SIZE][/FONT][/COLOR]

Have you done that in excel yet?

 

[JoeRooney]

Hi , yes I have checked this reference and still receive the same error on line "Set Db = OpenDatabase(Path) ' Open Database" , I have tried both .mdb & .accdb with the filepath but both receive the unrecognizable error.