Current recordset does not support updating vba supermarket dating
I added the above Cursor Location to see if problem went away, but it didn't. Michael Lee I have changed the above slightly but I'm getting the same results. Bob Just remove the space between faq and 222 - like this FAQ222-3670 ________________________________________________________________If you want to get the best response to a question, please check out FAQ222-2244 first'If we're supposed to work in Hex, why have we only got A fingers? Execute Here is the Stored procedure: CREATE PROCEDURE [dbo].[nb_Get Insurance]@Patient ID nvarchar(12),@Admission Date smalldatetime ASSelect Key Id, Patient ID, Admission Date, Insurance_Name, Insurance_Number From Insurance Where Patient Id = @Patient Id and Admission Date = @Admission Date GOI do have the Cursor Location set for the connection object. (Read up on "disconnected recordsets" if you're interested.) I did an faq called "nickel tour of ado's different cursor types" (faq 222-3670, sorry, I haven't learned how to plug the hyperlink in here) if you're interested. Execute "DELETE * FROM REGION" 'Open Excel Connection Set cnn Excel = Server. OLEDB.12.0; Data Source=" & str Access File Path & "; Persist Security Info=False;" cnn Access. Here is an Access query which mimics what I think you're trying to do.The following is a chopped down version of my code... Open "Driver=; Dbq=c:\Website Projects\Exit Cash Flow\log M.mdb;" str SQL = "Select * From Main Where Username = '" & str Query & "' and Domain = '" & str Domain & "'" Set rs Main = cn. Close Set rs Main = Nothing ======= End of Code ========= .mdb;" str SQL = "Select * From Main Where Username = '" & str Query & "' and Domain = '" & str Domain & "'" rs Main. Execute (str SQL) method returns a read-only recordset. Open str SQL, cn) and that took me a few minutes to figure out why I was getting a '3001' error... I have removed everything except the code dealing with the recordset... Although I don't fully understand your sample code, you seem to be opening one recordset for the Access table and another for the Excel worksheet, then moving through the Excel recordset and inserting (.Hi, Why are u "Late Binding" the Object by creating...? EDIT: str Archive Path is previously set directly in code, and does end with a "\".
Connection strmess = "SELECT * from tblinvoicelines WHERE invoice=" & new Invo Set red1 = cnp.
OLEDB.12.0;" & _ "Data Source=" & str Excel File & ";" & _ "Extended Properties=" & Chr(34) & "Excel 12.0 Xml; HDR=Yes; IMEX=1" & Chr(34) & ";" Response. Open "Select * from [REGION$]", cnn Excel, ad Open Static Response.
Write "Access connection opened" Const ad Open Static = 1 Const ad Lock Optimistic = 3 Const ad Cmd Text = &H0001 ' Load ADO Recordset with Access Data Set rst Access = Server. Write "Access Recordset loaded" ' Synchronize Recordsets and Batch Update Do Until rst Excel.
It would be better to use a set-based approach so you import all the worksheet rows as a single operation.
OLEDB.12.0;" & _ "Data Source=" & str Excel File Path & ";" & _ "Extended Properties=" & Chr(34) & "Excel 12.0 Xml; HDR=Yes; IMEX=1" & Chr(34) & ";" Response. Execute "INSERT INTO REGION IN 'C:\inetpub\wwwroot\Folder Name\App_Data\Access File Name.accdb' " & _ "Select col1,col2,col3,col4" & _ "from [REGION$]" ' Clean up cnn Excel. However, even if you got it working, you would still be left with a RBAR (Row By Agonizing Row) approach.