By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

Excel to Access, SQL and VBA

P: 10
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandRow29_Click()
  2.  
  3. Dim strSQL As String
  4.          Set appAccess = CreateObject("Access.Application")
  5.              appAccess.Visible = False
  6.  
  7.      If Cells(27, 4) = 0 Then
  8.         MsgBox "Please Check Users Initials are correct on 1st page of Workbook"
  9.         Exit Sub
  10.     End If
  11.     If IsEmpty(Range("A29").Value) = True Or Len(Cells(29, 1)) <> 5 Then
  12.       MsgBox "Please correct or enter a Job Number in Cell A29"
  13.       Exit Sub
  14.     End If
  15.        If IsEmpty(Range("C29").Value) = True Or Range("C29").Value = 0 Then
  16.       MsgBox "There are no hours in Cell C29"
  17.       Exit Sub
  18.     End If
  19.  
  20.     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")) & ")"
  21.  
  22.              Debug.Print Trim(Range("B27"))
  23.              Debug.Print Trim(Range("D27"))
  24.              Debug.Print Trim(Range("A29"))
  25.              Debug.Print Trim(Range("C29"))
  26.  
  27.     With appAccess
  28.       .OpenCurrentDatabase ("C:\LocalData\Projects\00000_Databases\financial\Databasetemp.accdb")
  29.       .DoCmd.RunSQL strSQL, dbFailOnError
  30.       .CloseCurrentDatabase
  31.     End With
  32.  
  33.     Set appAccess = Nothing
  34.  
  35.      Cells(29, 3).Font.Color = RGB(0, 128, 0)
  36.  
  37.      'CommandRow29.Enabled = False
  38.      MsgBox "Data uploaded to Projects Database"
  39.  
  40.  
  41. End Sub
My question is, is how do I identify the error. Any help appreciated.
Jun 24 '15 #1
Share this Question
Share on Google+
15 Replies


zmbd
Expert Mod 5K+
P: 5,397
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 :)
Jun 24 '15 #2

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jun 25 '15 #3

P: 10
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":

Expand|Select|Wrap|Line Numbers
  1. 10/05/2015 B27
  2. JST D27
  3. 00280 A29
  4. 5.52 C29
  5.  
2. Debug.Print of Passing line of data

Expand|Select|Wrap|Line Numbers
  1. 17/05/2015 B27
  2. JST D27
  3. 15694 A32
  4. 5 C32
3. This line also passes

Expand|Select|Wrap|Line Numbers
  1. 17/05/2015 B27
  2. JST D27
  3. 15687 A33
  4. 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.
Jun 25 '15 #4

P: 10
Debug.print of SQL Insert statement:

Expand|Select|Wrap|Line Numbers
  1.   strSQL = "INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) " & _
  2.              "VALUES (#" & Range("B27") & "#,'" & Range("D27") & "','" & Range("A29") & _
  3.              "'," & Range("C29") & ")"
  4.            Debug.Print strSQL = "INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) " & _
  5.              "VALUES (#" & Range("B27") & "#,'" & Range("D27") & "','" & Range("A29") & _
  6.              "'," & 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?
Jun 25 '15 #5

P: 10
And sorry this is turning into a stream of realisation.

So have added

Expand|Select|Wrap|Line Numbers
  1. If strql = False Then
  2.      MsgBox "There is a problem with the data, please check and retry"
  3.      End If
  4.      Exit Sub
to do what i needed to do to stop the code.
Jun 25 '15 #6

P: 10
So now I have come back to the project and had another go, and am more confused.
If I use
Expand|Select|Wrap|Line Numbers
  1. debug.print strsql
I get for a successful example
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) VALUES (#17/05/2015#,'JST','15694',5)
For a failing example
Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 25 '15 #7

zmbd
Expert Mod 5K+
P: 5,397
When the record fails to import, do you get the date field or nothing at all?
Jun 25 '15 #8

P: 10
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
Jun 25 '15 #9

P: 10
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.
Jun 26 '15 #10

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

Corrected SQL insert clause
Expand|Select|Wrap|Line Numbers
  1.   strSQL = "INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) " & _
  2.              "VALUES (#" & Format(Range("B27"), "mm\/dd\/yyyy") & "#,'" & Range("D27") & "','" & Range("A36") & _
  3.              "'," & Range("C36") & ")"
Jun 26 '15 #11

zmbd
Expert Mod 5K+
P: 5,397
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.
Jun 27 '15 #12

P: 10
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandRow29_Click()
  2.  
  3. Dim strSQL As String
  4. Dim dbs As DAO.Database, iCount As Integer
  5.  
  6.  
  7. If IsEmpty(Range("A29").Value) = True Or Len(Cells(29, 1)) <> 5 Then
  8.       MsgBox "Please correct or enter a Job Number in Cell A29"
  9.       Exit Sub
  10.  End If
  11.        If IsEmpty(Range("C29").Value) = True Then
  12.       MsgBox "There are no hours in Cell C29"
  13.       Exit Sub
  14.  End If
  15.  
  16.     Set appAccess = CreateObject("Access.Application")
  17.  
  18.     appAccess.Visible = False
  19.  
  20.  
  21.     strSQL = "INSERT INTO [Weekly Staff Costs] ([Week_Ending],[StaffInit],[Job_No],[Hrs]) " & _
  22.              "VALUES (#" & Format(Range("B27"), "mm\/dd\/yyyy") & "#,'" & Range("D27") & "','" & Range("A29") & _
  23.              "'," & Range("C29") & ")"
  24.    'B27 and D27 remain constant. A29 and C29 need to increment by 1 until the cells are empty
  25.     With appAccess
  26.  
  27.      .OpenCurrentDatabase ("F:\Matts_Stuff\database\ULAS_Financial_be.mdb")
  28.      Set dbs = CurrentDb
  29.  
  30.   dbs.Execute strSQL, dbFailOnError
  31.   iCount = dbs.RecordsAffected
  32.  
  33.       .CloseCurrentDatabase
  34.     End With
  35.  
  36.     Set appAccess = Nothing
  37.  
  38.     If iCount = 0 Then
  39.     MsgBox "No records uploaded. Please check week ending date, and staff initials are valid"
  40.     Exit Sub
  41.     Else
  42.     Cells(29, 3).Font.Color = RGB(0, 128, 0)
  43.     MsgBox iCount & " Time Sheet Record uploaded to Projects Database"
  44.      End If
  45.      Exit Sub
  46.  
  47. End Sub
Jun 28 '15 #13

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jun 30 '15 #14

P: 10
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
Jun 30 '15 #15

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 1 '15 #16

Post your reply

Sign in to post your reply or Sign up for a free account.