Erratic behaviour (1 Viewer)

matt beamish

Registered User.
Local time
Today, 11:10
Joined
Sep 21, 2000
Messages
208
Hi all,
I have some SQL code I wrote last year that helps us transfer data from Excel timesheet spreadsheets into a backend access database. Recently the code has started to stop working, but quite erratically (i.e. for one user the code fails to run, but for another it will, despite Permissions being Group based). I need to be able to trap an error more clearly than I am doing now, to understand why the sql is failing, but I am not competent to do this just yet. Might anyone be able to give me a hand?
thanks
Matt
 

Ranman256

Well-known member
Local time
Today, 07:10
Joined
Apr 9, 2015
Messages
4,337
Use a query, it will show you the error, unlike SQL.
If using code, put a trap in the code...
Code:
Sub MyRoutine()
On error goto ErrLbl

'Code
'Code

Exit sub
ErrLbl:
MsgBox err.description,,err
End sub
 

matt beamish

Registered User.
Local time
Today, 11:10
Joined
Sep 21, 2000
Messages
208
Thanks very much for this, I have now adapted my code
regards
Matt
 

matt beamish

Registered User.
Local time
Today, 11:10
Joined
Sep 21, 2000
Messages
208
I now have the error -

3192
'Could not find output table 'my table name'.

So sometimes, the back end table can't be located - perhaps due to a network error.

The procedure has been working for 6 months with a few hundred successful runs per week, and has only raised an error now.

thanks again
Matt
 

Rx_

Nothing In Moderation
Local time
Today, 05:10
Joined
Oct 22, 2009
Messages
2,803
Please explain.
This Excel upload to a Table: is this an Access Form opening an Excel timesheet?
Then does it upload to a networked Access table or an Access Linked Table to SQL Server?


Since this error is not constant, consider an Error Logging function.
Instead of just a message box, log any error on a local table and attempt to log it on a networked table. Be sure to use error recovery code to exit gracefully.
http://www.access-programmers.co.uk/forums/showthread.php?t=191742&highlight=LogUsage

Assuming your DB has an application Front End with Linked Tables to a Networked back-end DB:
Do how do Users (clients) reach the front end?
For example, do they each start up the front end from a Network location?
(if so, many members can discuss why this causes intermittent issues).

Or (best) does each user have a front-end downloaded locally to each PC, then the user launches a local copy of Access to consume the Excel timesheet?

Thanks, these answers may help identify the intermittent issues.
 

matt beamish

Registered User.
Local time
Today, 11:10
Joined
Sep 21, 2000
Messages
208
1. Excel spreadsheet with VBA code triggered by button click to run SQL
2. This uploads data from Excel spredsheet to Access mdb back end on shared network drive.
3. The front end that is used to access the back end is (shame on me) a single front end that is used by multiple users. I *know* that all the advice is against this, but it is what we have done for donkeys years, and it hasn't caused a problem I know of.
4. I did consider tracking how many users had the back end open when the error occurs, and this is something I can do with the "Who is connected" utility

thanks
 

Minty

AWF VIP
Local time
Today, 11:10
Joined
Jul 26, 2013
Messages
10,354
3. The front end that is used to access the back end is (shame on me) a single front end that is used by multiple users. I *know* that all the advice is against this, but it is what we have done for donkeys years, and it hasn't caused a problem I know of.
^^This^^ will cause you problems. Despite it not having bitten you in the past I suspect it is now.

It is not difficult to give everyone a local copy of the front end, particularly if you have group policy running.
 

Rx_

Nothing In Moderation
Local time
Today, 05:10
Joined
Oct 22, 2009
Messages
2,803
Minty, I think you are on to something.
If I understand, the code is actually in Excel. At least the Client is on each computer.
There might be an opportunity to check if the networked table is accessible first. Then use the error code to allow three or four tries after a short 3 second delay.
If it fails 4 times (12 seconds) then there may be a bigger problem.

In the past, I have built systems that harvested thousands of Excel "reports".
However, the key word is that this has been working. Try and make a minimal change to keep it the same. Sometimes that gives the developer breathing room to look at a redesign.
 

matt beamish

Registered User.
Local time
Today, 11:10
Joined
Sep 21, 2000
Messages
208
Hi folks.
In the interim this has continued to be erratic in success - perhaps 40% failure, which can be overcome by closing and opening the Excel file.
After ensuring that all other users had closed any front end connections to the target backend, so ensuring that the shared front end connections are not an issue, we tried uploading again from Excel.

A trapped error this time is
91
Object variable or With block variable not set

Any pointers?

thanks people
Matt
 

Minty

AWF VIP
Local time
Today, 11:10
Joined
Jul 26, 2013
Messages
10,354
Is the Excel file also on a shared network path ? I assume so but, is it open, or left in an a open state.

Can you force it to be opened in a read only state ?
 

matt beamish

Registered User.
Local time
Today, 11:10
Joined
Sep 21, 2000
Messages
208
Hi.
Yes, the Excel file is stored on a network share.
Personnel have a timesheet each which they fill in. An admin person then opens each time-sheet, does some validating and totalling/editing, and then performs the upload.
So the sheet needs to be modifiable to be useful. Are you suggesting I set one as read only after editing, and attempt the uploads to see if the error is removed?
thanks
Matt
 

Minty

AWF VIP
Local time
Today, 11:10
Joined
Jul 26, 2013
Messages
10,354
Yes - That would be the idea. It just removes another potential network issue.
Out of interest do you link to the Excel sheet or simply import it ?
 

matt beamish

Registered User.
Local time
Today, 11:10
Joined
Sep 21, 2000
Messages
208
OK will try - thanks.

I wrote a piece of SQL to pick up individual values, and with an Insert statement, append to a back end table. This code is repeated for 6 buttons/rows. Ideally the code would scroll through rows until values were null, but that is too clever for me!
thanks again
Matt

Code:
Private Sub CommandRow29_Click()

Dim strSQL As String
Dim dbs As DAO.Database, iCount As Integer, weektxt As Date, usertxt As String, nametxt As String

weektxt = Format(Range("B27"), "mm\/dd\/yyyy")
usertxt = Range("D27")
nametxt = Range("B3")

On Error GoTo ErrorMessage:

If IsEmpty(Range("A29").Value) = True Or Len(Cells(29, 1)) <> 6 Then
      MsgBox "Please enter valid Job Number in Cell A29" & vbNewLine & "Check the format is 00/000"
      Exit Sub
 End If
       If IsEmpty(Range("C29").Value) = True Or (Range("C29").Value) = 0 Then
      MsgBox "There are no hours/0 value 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 (#" & weektxt & "#,'" & usertxt & "','" & Replace(Range("A29"), "/", "") & _
             "'," & Range("C29") & ")"
     With appAccess
 
     .OpenCurrentDatabase ("\\pathtomybackend\database_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. Check that the Job No is valid, the Weekending Date is set to a Sunday, and that both Weekending date and Staff Initials are set up in the projects database."
    Exit Sub
    Else
    Cells(29, 3).Font.Color = RGB(0, 128, 0)
    Me.CommandRow29.Enabled = False
    MsgBox iCount & " record for " & usertxt & " (" & nametxt & ")" & "  uploaded to projects database."
     End If
     Exit Sub
     
ErrorMessage:
 MsgBox "This upload has failed. Check that the Job No is valid/confirmed, and that both Weekending date and Staff Initials match values in the projects database."
'Added code below to pick up error number 12/1/2016  
MsgBox Err.Description, , Err
 Exit Sub
 
End Sub
 

Minty

AWF VIP
Local time
Today, 11:10
Joined
Jul 26, 2013
Messages
10,354
Okay - so you are running this from Excel - not Access.
Have you thought to import the raw data into Access and do the work there?

Or better still make a small distributable front end to allow the staff to enter the information into a table, that the managers can then approve, all in the database ?

You can install runtime Access for nothing...
 

matt beamish

Registered User.
Local time
Today, 11:10
Joined
Sep 21, 2000
Messages
208
Okay - so you are running this from Excel - not Access.
Have you thought to import the raw data into Access and do the work there?

Or better still make a small distributable front end to allow the staff to enter the information into a table, that the managers can then approve, all in the database ?

Well this would mean a total change in system which will take a lot of effort to sort out, and meet much resistance. My SQL solution joining Excel to Access was elegant and efficient - I'm not going to abandon it just yet!

thanks for your help
 

matt beamish

Registered User.
Local time
Today, 11:10
Joined
Sep 21, 2000
Messages
208
For anyone else who might just get the same problem in the future, a quick fix is for the user sending the data from Excel to Access using SQL like this, to Open the target database. This has sorted the problem for the time being. Not elegant, but it works.
 

Rx_

Nothing In Moderation
Local time
Today, 05:10
Joined
Oct 22, 2009
Messages
2,803
Sorry, it has been a busy season on my end.
Just quickly reviewed th catch up and thanks for the code examples.

I have posted on this subject before. When using the With (excel object) statement followed by the statements .Property before the End With - if a single period is missing, something strange happens.

The first time, the compiler actually runs the code. However, an orphan thread can open where the .property was missing a period.
the second time the application runs, an error like the one you described can appear.
Close the application, run again and it runs the first time.

One key to investigate is to turn on Task Manager.
When running the Excel VBA, does only one instance of Excel open from automation, then close (go away) when running your applicaiton?

Now, this code runs from Excel so the automation is Access as opposed to Excel being automated from Access.

.OpenCurrentDatabase ("\\pathtomybackend\database_be.mdb")
Set dbs = CurrentDb

I think this may be running the first time as sometimes automation tends to bind things the first pass. But, I don't see how this is properly bound from the same point of view.
From within excel, the MDB can be opened with no reference to MS Access. Keep the code and all reference to DAO.

Please post an update on your code, I will have time to look at it tomorrow.:D
 
Last edited:

Users who are viewing this thread

Top Bottom