Excel to Access, SQL and VBA

[baffle]

First post from an amateur newbie......

Have put this code together to upload data from an Excel spreadsheet into an Access backend. From one spreadsheet the data enters correctly, but from another which is slightly adapted, the data does not appear. I can see that the link is almost being made, as my PK autonumbers are incrementing leaving gaps the failed inserts are causing. Having used a debug.print I wondered if leading/trailing spaces were messing things up so I used Trim, but still no joy.
Here is the code - thanks to a post last year from ZMDB for the basis of this.

Private Sub CommandRow29_Click()
 
Dim strSQL As String
         Set appAccess = CreateObject("Access.Application")
             appAccess.Visible = False
 
     If Cells(27, 4) = 0 Then
        MsgBox "Please Check Users Initials are correct on 1st page of Workbook"
        Exit Sub
    End If
    If IsEmpty(Range("A29").Value) = True Or Len(Cells(29, 1)) <> 5 Then
      MsgBox "Please correct or enter a Job Number in Cell A29"
      Exit Sub
    End If
       If IsEmpty(Range("C29").Value) = True Or Range("C29").Value = 0 Then
      MsgBox "There are no hours in Cell C29"
      Exit Sub
    End If
 
    strSQL = "INSERT INTO [Weekly Staff Costs]([Week_Ending],[StaffInit],[Job_No],[Hrs]) " & "VALUES (#" & Trim(Range("B27")) & "#,'" & Trim(Range("D27")) & "','" & Trim(Range("A29")) & "'," & Trim(Range("C29")) & ")"
 
             Debug.Print Trim(Range("B27"))
             Debug.Print Trim(Range("D27"))
             Debug.Print Trim(Range("A29"))
             Debug.Print Trim(Range("C29"))
 
    With appAccess
      .OpenCurrentDatabase ("C:\LocalData\Projects\00000_Databases\financial\Databasetemp.accdb")
      .DoCmd.RunSQL strSQL, dbFailOnError
      .CloseCurrentDatabase
    End With
 
    Set appAccess = Nothing
 
     Cells(29, 3).Font.Color = RGB(0, 128, 0)
 
     'CommandRow29.Enabled = False
     MsgBox "Data uploaded to Projects Database"
 
 
End Sub

My question is, is how do I identify the error. Any help appreciated.

[zmbd]

the first thing to do is to make sure that the imported data is the correct data type for the field...

we also need to see how the SQL in line 20 is resolving. Debug print that :)

[NeoPa]

Absolutely.

One trick, when the information coming back from executing SQL is a little paltry, is to take the resolved SQL and create a QueryDef from it (Open a new QueryDef; Switch to SQL view; Paste the SQL in; Switch back to Design view). When run, this typically gives more helpful information as to why some records failed.

[baffle]

Thanks kindly for your responses.

B27 is a date field, formatted as long date. Target, medium date, ref. integrity to a weeks table is set.
D27 is user initials, text. Target text, ref. integrity to a staff table is set.
A29 is a text string needing 5 digits, formatted as text in excel. Target, text, ref. integrity set to a jobs table. Some of my failures may well have stemmed from this (although this is not proven to be the only cause of failure) as the same Job no fails from different sheets.
C29 is decimal hours with origin in neighbouring cell B29, rounded to 2 in excel (C29=ROUND(B29*24,2); Format of B29=[h]:mm. Target database hrs is Number, double, only 1 dec place (though looking at the data this has not been how the data is stored as much has 2 dec places.



1. Debug print of a failing line of data using Debug.Print Trim(Range("B27")); " B27"
Debug.Print Trim(Range("D27")); " D27"
Debug.Print Trim(Range("A29")); " A29"
Debug.Print Trim(Range("C29")); " C29":

10/05/2015 B27
JST D27
00280 A29
5.52 C29
 

2. Debug.Print of Passing line of data

17/05/2015 B27
JST D27
15694 A32
5 C32

3. This line also passes

17/05/2015 B27
JST D27
15687 A33
32.95 C33

4. Re QueryDef/SQL
THis may be a very daft question, but how do I view a query definition to switch to SQL view from with Excel 2010? I could understand putting the code into SQL from Access and switching to design view if that's what you mean.

5. Very importantly, I need to build in something that does not give a user the impression that the code has worked,when it hasn't. ALthough the data is not getting posted into the database, the sub runs from start to finish, disables and colours the bits that would give a user the impression the upload had worked. Do I need to count records before and after upload to check that entries have been made and stop the code accordingly?

thanks again for your help.

[baffle]

Debug.print of SQL Insert statement:

  strSQL = "INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) " & _
             "VALUES (#" & Range("B27") & "#,'" & Range("D27") & "','" & Range("A29") & _
             "'," & Range("C29") & ")"
           Debug.Print strSQL = "INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) " & _
             "VALUES (#" & Range("B27") & "#,'" & Range("D27") & "','" & Range("A29") & _
             "'," & Range("C29") & ")"

Immediate:
False 'for a line that fails
True ' for a line that passes

So to answer my own question, I can include an error message, and exit the sub on the basis of this.
But how to find the problem with the data? Break my SQL into chunks?

[baffle]

And sorry this is turning into a stream of realisation.

So have added

If strql = False Then
     MsgBox "There is a problem with the data, please check and retry"
     End If
     Exit Sub

to do what i needed to do to stop the code.

[baffle]

So now I have come back to the project and had another go, and am more confused.
If I use

debug.print strsql

I get for a successful example

INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) VALUES (#17/05/2015#,'JST','15694',5)

For a failing example

INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) VALUES (#03/05/2015#,'JST','12907',1.5)

The difference above is the decimal in the number value.,
But at the same time, this example also passes

INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) VALUES (#17/05/2015#,'JST','15687',32.95)

Why did I get True or False responses to Debug.Print strsql previously? Sorry, really showing my ignorance here.

[zmbd]

When the record fails to import, do you get the date field or nothing at all?

[baffle]

I get nothing, but the target table has some required fields, so am I right to think if not all fields are there for a record, the process would fail. Perhaps I should try a target table with no required fields to more easily track what is happening.
thanks

[baffle]

Think I am getting there with this, having tried exporting to tables with no ref integrity for the dates (which need to correspond with Sundays). A problem is that the SQL is assuming my dates are mmddyyyy when they are in fact ddmmyyyy. I need to force the format into ddmmyyyy.

[baffle]

So with being upfront that the date format coming through the SQL is mmddyyyy, this seems to resolve the problem

Corrected SQL insert clause

  strSQL = "INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) " & _
             "VALUES (#" & Format(Range("B27"), "mm\/dd\/yyyy") & "#,'" & Range("D27") & "','" & Range("A36") & _
             "'," & Range("C36") & ")"

[zmbd]

Ok, that is why I specifically asked about the date.

Access handles all dates via code as #MM/DD/YYYY#
Please read thru Allen's discussion covering this topic:
International Dates in Access

Which by happy circumstances you seem to have found the solution (?) as given in his article.

[baffle]

Here is my code now adapted to include Records Affected and a message telling the user if a record has uploaded. Might anyone be kind enough to
A. look at this for howlers, as I am a novice at this game
B. Suggest how I might adapt this to scroll through a number of records until an empty cell reached? In my code, I have indicated 2 cells that remain constant (Date and Initials) and 2 that change for each line of data (Job Number and Hrs)
thanks in advance

Private Sub CommandRow29_Click()
 
Dim strSQL As String
Dim dbs As DAO.Database, iCount As Integer
 
 
If IsEmpty(Range("A29").Value) = True Or Len(Cells(29, 1)) <> 5 Then
      MsgBox "Please correct or enter a Job Number in Cell A29"
      Exit Sub
 End If
       If IsEmpty(Range("C29").Value) = True Then
      MsgBox "There are no hours in Cell C29"
      Exit Sub
 End If
 
    Set appAccess = CreateObject("Access.Application")
 
    appAccess.Visible = False
 
 
    strSQL = "INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) " & _
             "VALUES (#" & Format(Range("B27"), "mm\/dd\/yyyy") & "#,'" & Range("D27") & "','" & Range("A29") & _
             "'," & Range("C29") & ")"
   'B27 and D27 remain constant. A29 and C29 need to increment by 1 until the cells are empty
    With appAccess
 
     .OpenCurrentDatabase ("F:\Matts_Stuff\database\ULAS_Financial_be.mdb")
     Set dbs = CurrentDb
 
  dbs.Execute strSQL, dbFailOnError
  iCount = dbs.RecordsAffected
 
      .CloseCurrentDatabase
    End With
 
    Set appAccess = Nothing
 
    If iCount = 0 Then
    MsgBox "No records uploaded. Please check week ending date, and staff initials are valid"
    Exit Sub
    Else
    Cells(29, 3).Font.Color = RGB(0, 128, 0)
    MsgBox iCount & " Time Sheet Record uploaded to Projects Database"
     End If
     Exit Sub
 
End Sub

[NeoPa]

Did you ever find out why some lines succeeded while others failed? You seemed to overlook the possibility that the [Job_No] field was a Foreign Key. In such a case then the value in that field could cause it to fail regardless of the value in [Hrs].

To get more information you can follow the instructions of my first post (#3) and take the failing SQL (Such as that from the third code block in your post #7.) and paste it into a QueryDef. When you have a QueryDef open you can switch to SQL View at any time by selecting the Home toolbar and then the View dropdown to see and select SQL View.

When you execute this SQL from the QueryDef you should get more helpful error information.

[baffle]

Hi. The issue was, as I indicated in posts 9 and 10, that of dates. My excel dates were formatted as ddmmyyyy. My target dates are formatted as ddmmyyyy. I now understand sql can only carry dates in mmddyyyy. My target dates are keys and are always and only sundays. My procedure would succeed with dates that could not be mmddyyyy (ie 15/06/2015) but fail with those that could be mmddyyyy (ie 03/05/2015) as those dates that could be converted from UK to US format were, and then these were no longer Sundays (ie 3rd May 2015 is but 5th March isn't). Once I had include the specific statement that the dates weer being carried mmddyyyyy by the SQL, the issue was resolved, and I am just a little bit wiser!
I will have another go with your kind suggestions.
Cheers

[NeoPa]

Excellent :-)

There is more info on this to be found at Literal DateTimes and Their Delimiters (#) Post #2 contains an alternative approach that may be more to your liking as it shows the three separate factors (Y, M & D) in a logical order. Similar to how we show dates in The UK but in reverse.