ado

I'm creating a workbook to query data from an Access database. Part of the process to get the data I need, requires creating one query (A), and then creating a query (B) to the query (A). I've not come across the need to save a query (from Excel) to Access before. I'm sure this can be done, can...

I'm running an ADO query through Excel to insert up to 50k rows into a table. The query uses dSUM to get a running total for items by date, so it's likely that it is "calculation heavy". Even running directly in Access, it takes a moment to complete. When running it in Excel however, I get the...

I have the code (which works sometimes) to export an Excel named range to an SQL Database existing table, which I have pasted below: Sometimes it works, sometimes it does not. Error is TempRange is not found by VBA (Object not found). I have no idea why it gives error randomly. Pl help...

Hello guys, I am learning VBA by myself and I saw some codes that have the expression provider in it, like this one Connection.Provider = "Microsoft.ACE.OLEDB.12.0" I can not understand what is the function of the Provider in this case and where I can find the code in the...

I created a SQL query that gathers information for users to determine which items in our system need to be inactivated. The user receives an Excel workbook that has a table with a data connection to the SQL Server database that pulls in the information they need (via stored proc). I want to...

I've managed to query my MS Access database through Excel using the ADO method. I'd like to learn how the syntax differs for an INSERT query; specifically when inserting values from a form. Here is the code I use for a SELECT query: Set myConnection = New ADODB.Connection Set myResults = New...

Hi, I'm querying an OLAP source using ADO from VBA with code similar to below : Set rs = New Cellset With rs .Open sQry, cnn End With Usually I dump the Cellset into an array and then dump to the worksheet in one pass. This doesn't work with a big query (1m rows+) due...

Hi everyone, I don't use ADO much but I've inherited something that is using ADO and I've had a few problems. Our network infrastructure isn't the greatest here and some times the connection to our shared drives momentarily drops. It doesn't unmap the drives or anything but we get an error in...

I want to learn to use Excel ado, check if it is possible to introduce a book or instructional videos Thank

Hi, I'm having issues with the following code: Dim date1 as Date [..] date1 = date - 20 '20 days ago oCm.CommandText = "Select * From mytable where resource = """ & resourcename & """ AND " & _ "date1 >= #" & date1 & "#" Set rs = oCm.Execute(iRecAffected) this statement is supposed to...

In my access, I've had to set a lot of fields to have default properties to avoid runtime errors (and since it's Excel based ADO objects, I have no Nz() function) But I also have a bunch of text and memo fields, which when I just wrote a 'duplicate record' function, was returning the 'Invalid...

I've created a tool that works creating Excel subsets worksheets of lager Excel worksheets. My OS is windows 7 Here is the connection info: Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & SourceXLSX & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""" This has worked...

I cannot copy date formatted cells (that actually contain dates, some contain text) from one sheet to another through an ADO connection. I am not interested in using excel functions, only VBA. Thank you!!! [VBA Code] ws3.Cells(row, 2).Value = rs2.Fields.Item("Name") ws3.Cells(row, 3).Value =...

Howdy I've built a little app to reside on our shared-drive, which is designed to store relatively few records, and accessed by probably not more than 4-6 people at any single one time. Only one record can be Added/Edited/Deleted at a time (there are no batch delete/edit/add functions) Here's...

My current Access + Excel solution: I use an Access database to create two pass through queries. Each pass through query selects records from different AS400 data source. I then use a third (regular... not pass through) query to INNER JOIN the two tables on a common field. The records returned...

Hi All. I currently have a workbook that has VBA functions that calculates values by looping through large amounts of data in a few large excel sheets. I want to transfer the data from these sheets into Access and then bring data into the VBA code via an array from the data in Access rather than...

I am having trouble setting up a query for a recordset that will list duplicates for an automated process to clean up. I am using Excel 2010 with Microsoft ActiveX Data Objects Recordset 6.0 Library and Microsoft ActiveX Data Objects 6.1 Library as references. So far, I have chopped the query...

I have created a pivot table from an ADO recordset using VBA. The pivot table is created and everything seems normal. However when I start adding row or column fields, the title cells display in the sheet, but not the individual records. They records will not appear until I add a datafield to...

Am at my wits' end. what is wrong with the code below? It is giving me an error "ADODB Connection --> Operation is not allowed when the object is closed." Public Sub GetTextFileData() Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer Dim strSQL As String, StrFolder As String...

I have a table, name myTable in access database in desktop\jwb.accdb. the table has more then 50 fields. the name of the fields: answer1, answer2,....answer50. And I have data in excel column A1 to A50 that I want to add to these fields. data in A1 to answer1 data in A2 to answer2 data in A3 to...