423,849 Members | 1,901 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,849 IT Pros & Developers. It's quick & easy.

Updating Update Query from Excel Cell

P: 1
hi, I have been stuck with this part here for days...
basically I have been trying to use VBA in excel to "export" a value as a criteria to an update query in Access. I have googled part of the solution but I got stuck with this "error 3265, item not found in this collection"

Expand|Select|Wrap|Line Numbers
  1. Sub importTblCurrent()
  2.     Dim tsSH As Worksheet
  3.     Dim cn As ADODB.Connection
  4.     Dim rs As ADODB.Recordset
  5.     Dim strConn As String
  6.     Dim strSQL As String
  7.     Dim wkComm As Date
  8.     Dim sDC As String
  9.     Dim mydatabase As DAO.Database
  10.     Dim myquerydef As DAO.QueryDef
  11.     Dim myrecordset As DAO.Recordset
  12.     Dim ws As DAO.Workspace
  13.     Dim wkCommPara As Date
  14.     'Dim i As Integer
  15.  
  16.  
  17.     Set ws = DBEngine.Workspaces(0)
  18.     Set tsSH = Sheets("Test")
  19.     Set cn = New ADODB.Connection
  20.     Set rs = New ADODB.Recordset
  21.     strConn = "C:\CasLeave.accdb"
  22.     wkCommPara = Sheets("Test").Range("I1")
  23.     Set mydatabase = ws.OpenDatabase _
  24.     ("C:\CasLeave.accdb")
  25.     Set myquerydef = mydatabase.QueryDefs("qryapplyreq")
  26.  
  27.     With myquerydef
  28.     .Parameters([wkComm]) = wkCommPara
  29.     End With
  30.  
  31.     cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strConn & ";"
  32.     cn.Execute "qryDelCurrent"
  33.     cn.Execute "qryApplyMaster"
  34.     cn.Execute "qryApplyReq"
  35.     strSQL = "SELECT * from tblCurrent"
  36.  
  37.  
  38.     rs.Open strSQL, cn
  39.  
  40.     Sheets("Test").Range("c6:I65000").ClearContents
  41.  
  42.     tsSH.Range("A6").CopyFromRecordset rs
  43.  
  44.     rs.Close
  45.     cn.Close
  46.     Set rs = Nothing
  47.     Set con = Nothing
  48.     MsgBox ("Casual Roster created for: " & tsSH.Range("I1").Value)
  49. End Sub
  50.  
attached with my query setup

please help? I have been stuck for days
Attached Images
File Type: jpg Capture.jpg (20.0 KB, 27 views)
Apr 17 '18 #1
Share this question for a faster answer!
Share on Google+

Post your reply

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