Data not sent to Access database

[Derek McGill]

All help will be welcome fixing macro which is to transfer a Table called "Look_up_Table2" to Access, Error is in reading first field name.

 

[dan_l]

what's the actual error message?

 

[Derek McGill]

No error msg just no data sent to Access.
When i go to Fld1 it skips to onError then back to Fld2 then to onError and so on.

 

[Debaser]

There aren't any named ranges matching the names you're using for the headers.

 

[Derek McGill]

Are they not the "Table" headers ?

 

[Debaser]

No. If the header names match the field names in the database, why not just use them directly instead?

By the way, when you write this in VBA:

Dim Fld1, Fld2, Fld3, Fld4, Fld5, Fld6, Fld7 As String

only Fld7 is declared as a String, all the others are Variant. You have to specify the type for each variable.

 

[Derek McGill]

No. If the header names match the field names in the database, why not just use them directly instead?

How is this achieved ?

 

[Debaser]

Instead of this:

Fld1 = Range("FullName")
Fld2 = Range("Initial")
Fld3 = Range("Surname")
Fld4 = Range("Rating")
Fld5 = Range("Kfactor")
Fld6 = Range("PhoneNo")
Fld7 = Range("MobileNo")

use this:

Fld1 = "FullName"
Fld2 = "Initial"
Fld3 = "Surname"
Fld4 = "Rating"
Fld5 = "Kfactor"
Fld6 = "PhoneNo"
Fld7 = "MobileNo"

 

[Derek McGill]

Ok got that, Now error is on this line:-
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sDbFile & ""

 

[Debaser]

Jet has no clue about the accdb format. Change the provider to:

Provider=Microsoft.ACE.OLEDB.12.0

 

[Derek McGill]

Getting better !, Still no data transfer now goes to ErrHandler after con.Execute Sql

 

[Debaser]

Comment out the On Error statement so that we can determine what the actual error message is.

 

[Derek McGill]

Access engine could not find the object MyRange

 

[Debaser]

Does MyRange exist in your actual file and what does it refer to?

 

[Derek McGill]

It refers to the Table called "Look_up_Table2" which is the data i am trying to send to Access

 

[Debaser]

I mean what is the exact value in the RefersTo box in the Name Manager.

 

[Derek McGill]

=Gradings!$A$2:$G$350

 

[Debaser]

Sorry for the delay.

There are a few changes required to your code and workbook.

First, your range needs to include the header row, so it should start at A1.

Second, sWbkName should include the extension, so:

sWbkName = ThisWorkbook.Name

Last, your connection to the workbook should specify Excel 12 format:

" [Excel 12.0;HDR=YES;Database=" & sThisWbk & ";].[MyRange];"

 

[Derek McGill]

Thanks Debaser
Code working now.
I also had to change MyRange slightly by adding
Set Table1 = ActiveSheet.ListObjects("MyRange") into the code
Derek