Excel to Access, Insert SQL count records (1 Viewer)

[matt beamish]

Have used a technique to insert records into an Access database from an Excel spreadsheet, using Insert SQL.
I need to get verification that records have been appended, but understand that Insert SQL does not support this.

Can I use DAO in the same function?
The Access database is referenced from the spreadsheet using a Public function

Public appaccess As Access.Application

And within my function

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") & ")"
                           
    With appaccess
      .OpenCurrentDatabase ("C:\LocalData\mgb3\Projects\00000_Databases\financial\Databasetemp.accdb")
      .DoCmd.RunSQL strSQL, dbFailOnError
      .CloseCurrentDatabase
    End With
    
    Set appaccess = Nothing

When I am referencing the database remotely like this, can I also use

Dim db As DAO.Database 
Set db = CurrentDb

and then execute the SQL, and use a .RecordsAffected command?

Any help appreciated thanks.

 

[Rx_]

Looks like your on the right track. Let me aske a couple of general questions based on experience about your business process. It is OK if it won't work for your business situation.

It appears that the plan is to add vba code to the Excel Workbook to accomplish this. In general, my preference is to avoid this to prevent users from dealing with that nagging "contains macro code" warning.
For about the same coding effort, an Access application could find Excel on a local drive or network drive and accomplish the same thing. This often seems easier to have an Excel person "save as" a report to a network drive where the data can be harvested. My preference is to read the data from Excel - transact it to some data mart (it doen't need to be on the Access program runing the data harvest, it could be to a different Access, SQL Server, Oracle, ... databae).
Then, once the Excel data has been harvested, mark specific Cell or Excel Workbook property as "Data was Migrated on xxx date". Then, lock the Excel workbook for edits for example, or programitically move it to a network location the user who submitted it can't get to.
This provides a full circle transaction.

Your code looks like it would work. I am suggesting a change in the business process that is described.

Perhaps that doesn't fit your situation. My preference is to keep full logging so that when someone discovers things failed, it is easy to trace. Realize my past and present experience deals with legal laboratory samples, government inspections reports for enforcement, tax or regulatory compliance, or other situations where a tracable audit is highly prefered.

I don't want to be like that spoof movie where the police dective was cleaning out his desk and says "oh, look, there is that missing evidence on the Smith case, I guess he really was innocent on that case ten years ago. Well, you won't need to tell him, he was sent to the electric chair last week."

Over the two decades, I have seen all of the horror stories based on the assumption that Excel was being used as data input. It can make for efficient source entry so long as there is a good plan. For example, what do you do if the Excel person uploads the data, then the user makes a change, then uploads it again?

 

[matt beamish]

Thanks for replying to my post. I understand the problems you raise, and can envisage implemented a system as you suggest.
As this database if effectively a shadow (i.e it is not governing who gets paid what), I think this is appropriate. Today I have implemented the spreadsheet, and have already added to the foot of the code, arguments to, in the event of a successful upload, colour the submitted data a different colour and disable to the onclick button.
But thanks again for taking the time.

 

[matt beamish]

for anyone interested, this is my code which works:

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 ("\\..........mydatabase.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."
 Exit Sub
 
End Sub