Excel VBA calling Access Query not working (1 Viewer)

brianTheLion · Mar 11, 2015

Hello all,

This is my first post and I am an intermediate Access user. I will provide as much detail as I can regarding my dilemma. The issue is occurring towards the end of the process but perhaps someone can spot something else that could be causing it.

I have a project that consists of 3 components: an Access Database, an Excel VBA-enabled workbook (.xlsm) and an InfoPath form.

The InfoPath form is used to fill out requests that need to be completed by one of our departments. The data from the forms is directly linked to a database table via InfoPath and is working appropriately.

The Access table is the "house" for these incoming requests.The table has about 20 fields, 10 of which are populated with the InfoPath form data. The other 10 are blank because that is information the person handling the request fills in.

The Excel workbook has the table linked from Access. This acts as the "workqueue" for people handling the requests. This part also works appropriately. When a new request is submitted via InfoPath to the database, a simple RefreshAll in Excel brings in the new requests to the linked table without issue. This workqueue is also linked back to Access (External Data>Excel>Link to data source by creating a linked table.).

**SO BEGINS THE DILEMMA...**

My issue is that I need to get the data from the person working the requests in the Excel workqueue BACK to the main table in Access.

In Excel VBA, I have the Worksheet_Change Event triggering the following code, which connects to my database, and then depending on the column of the changed cell, triggers one of the queries that I have created in Access:

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

   Dim conn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim rst As ADODB.Recordset

   Set conn = New ADODB.Connection
   With conn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\cc.ad.cchs.net\world\Shared\PFS\Customer Service\Coding Review Forms\CS_Coding_Requests.accdb;"
        .Open
   End With

'Institute column on Kandi's Pro
If Target.Column = 11 Then
   Set cmd = New ADODB.Command
   With cmd
      .ActiveConnection = conn
      .CommandType = adCmdStoredProc
      .CommandText = "Kandi_Pro_institute"
   End With

   cmd.Execute
 
   Set rst = Nothing
   conn.Close
   Set conn = Nothing
End If

'Service Location column on Kandi's Pro
If Target.Column = 12 Then
   Set cmd = New ADODB.Command
   With cmd
      .ActiveConnection = conn
      .CommandType = adCmdStoredProc
      .CommandText = "Kandi_Pro_servicelocation"
   End With

   cmd.Execute
 
   Set rst = Nothing
   conn.Close
   Set conn = Nothing
End If

'Coder Notes column on Kandi's Pro
If Target.Column = 13 Then
   Set cmd = New ADODB.Command
   With cmd
      .ActiveConnection = conn
      .CommandType = adCmdStoredProc
      .CommandText = "Kandi_Pro_codernotes"
   End With

   cmd.Execute
 
   Set rst = Nothing
   conn.Close
   Set conn = Nothing
End If

'Start Date column on Kandi's Pro
If Target.Column = 14 Then
   Set cmd = New ADODB.Command
   With cmd
      .ActiveConnection = conn
      .CommandType = adCmdStoredProc
      .CommandText = "Kandi_Pro_startdate"
   End With

   cmd.Execute
 
   Set rst = Nothing
   conn.Close
   Set conn = Nothing
End If

End Sub

Example SQL Query created in Design View in Access. I joined the tables by ID (AutoNumber) and I want it to UPDATE the in_MainPro.institute field with the value in Kandi_MainPro.institute WHERE they are <> not equal to each other (ie update made on the workqueue<> blank field in Access..then update the blank value = to the value in the workqueue):

Code:

UPDATE in_MainPro INNER JOIN Kandi_MainPro ON in_MainPro.ID = Kandi_MainPro.ID SET in_MainPro.institute = [Kandi_MainPro].[institute]
WHERE (((Kandi_MainPro.institute)<>[in_MainPro].[institute]));

The problem I have run in to is that the first one works! (institute) Any change made in the institute column in Excel is immediately updated in the main table in Access.

I replicated the 'servicelocation' and 'codernotes' queries exactly as I did for institute, but these values are not updating in the main Access table!

I have scoured the web for forums resources, tutorials, etc. to no avail. Any help, guidance and/or suggestions would be so greatly appreciated! I look forward to hearing your input and retrieving my sanity! :banghead::banghead::banghead:

*Note: due to end users not having Access (request submitters OR request workers), it has to be done in this fashion- using Excel as the front end.

-Brian

JHB · Mar 12, 2015

Have you tried to step through your code to find out if the code got executed for Target.Column = 12 and 13?
Does the change for Target.Column = 14, ('Start Date column on Kandi's Pro) Update?
Next, try to comment out the code for Target.Column = 11 only for finding out if one of the other work (get updated)?
You show the SQL-string for the one which work, but you didn't the SQL-String for the two which doesn't work, (show it).
Do you have any error handling somewhere which could have the effect that you do not see a possible error?

Excel VBA calling Access Query not working (1 Viewer)

brianTheLion

New member

JHB

Have been here a while

Similar threads

Users who are viewing this thread