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.
15 2025 zmbd 5,501
Expert Mod 4TB
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 32,556
Expert Mod 16PB
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.
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.
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?
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.
So now I have come back to the project and had another go, and am more confused.
If I use
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 5,501
Expert Mod 4TB
When the record fails to import, do you get the date field or nothing at all?
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
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.
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 5,501
Expert Mod 4TB
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: MARK TURNER |
last post by:
Hi,
Can anyone point me in the right direction to learn how to
get real time updates in an Access form from an Excel spreadsheet?
I'm trying to link the following formula which exists in an...
|
by: Jim Heavey |
last post by:
Hello, I am using a generic AD Domain Account to create an excel spreadsheet,
but I am getting an "Access Denied" error when I attempt to instatiate the
excel object. The generic account does is...
|
by: jasmith |
last post by:
How will Access fair in a year? Two years? .... The new version of
Access seems to service non programmers as a wizard interface to
quickly create databases via a fancy wizard. Furthermore, why...
|
by: miffe |
last post by:
Greetings,
I've got an access database on a server, and in a client PC I've got an excel spreadsheet... I run a query from the client PC involving some maths to develop different price lists from...
|
by: vancehome |
last post by:
I have a series of macros running everynight creating tons of excel files (using transfer spreadsheet). I then have another program that zips them up and emails them all over the place. It then...
|
by: BFoxDDS |
last post by:
i'm sure this is simple to do, but i can't find the answer in the
search engines.
i have an access db with 400,000 records. i produce queries of 5,000
records at a time exported into an excel...
|
by: farhaaad |
last post by:
Hi everybody,
I just wanted to know if i can make a form in excel (the same as
access forms), so when i enter data in excel form it goes to a table
in access ? I mean when i enter a value in a...
|
by: bcr123 |
last post by:
Hello.
Could you please help with following problem in Microsoft Excel:
I have thousands or rows of 5min data that I need to convert to 15min and later to 20min data do some calculations.
...
|
by: TrevoriousD |
last post by:
hi
I have created a data link from access query to excel. I use excel pull data from access in order to do stats. the excel workbook always stays in the same folder as the access db. I want to be...
|
by: roshaan |
last post by:
i m very new to asp and working on a project using asp and access not asp.net……….
I have a small application in which after login, user can upload .xls file, then the record from this excel file...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| | |