1. B

    Excel ADODB Sql Query Execution taking hours when manipulate excel tables

    Hello All I have 28000 records with 8 column in an sheet. When I convert the sheet into ADODB database and copy to new excel using below code it is executing in less than a min Set Tables_conn_obj = New ADODB.Connection Tables_conn_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &...
  2. M

    Excel VBA: Copying one recordset to another

    I have been beating my head against the wall on this one for a couple of days. :banghead: I'm trying to get a recordset from SQL based on a query using ADO, replace all but the first four fields of the single record, and write it back to a table in SQL. I can read the recordset from SQL and I...
  3. M

    ADODB SQL Statement ORDER BY Statement

    Hi, I have been reading a lot about ADODB but I couldn't figure my problem. I hope someone here can help. I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function: Sub testInsert() Dim adoCommand As New ADODB.Command Dim...
  4. M

    Cannot import data from Excel 2013 into ASP file

    I am using Excel 2013, and I am following the example here: VBScript Scripting Techniques: Read Excel files without using Excel that reads in Excel data as an ADO record set to a classic ASP file using VBScript. I am not able to import all the Excel data successfully, and I need to know what I'm...
  5. J

    Updating Access Database with Excel File

    Hello, I've been trying to make this work for hours and hours and am finally giving up. Most of this code was found on the internet and I've attempted to make it work for my project but I keep getting errors. I use VBA with Excel quite often but never with Access before. Currently I am getting...
  6. E

    Trying to run stored SQL Procedure in VBA

    Hi. I am trying to run a stored SQL procedure in VBA. I have the below code. I get "Syntax error or access violation" on the objMyCmd.Execute line. I feel like I am not correctly pointing to the location of the stored procedure. Anyone have any ideas? The query I am trying to run is named "sql...
  7. K

    ADODB: Issues with a WHERE condition in a SELECT

    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...
  8. B

    Consolidating Multiple Access Databases into 1 SQL Server Express

    I basically have 6 separate Access databases. Every month I get a new Excel Data file that I copy and paste into database #1. I then run a macro that runs a series of queries on this data and then exports it to database #2. I then open database number 2 and run a macro that runs another series...
  9. ClimoC

    Odd Requery/Updatebatch behaviour

    Howdy I have an Excel Workbook with userforms, pivottables and the like. The backend is an Access db (accdb), with connection through ADODB Everything seems to work beautifully, except when I'm adding a new record. Here's the startup connection: Public Function TestBatchUpdate() Dim...
  10. G

    Excel VBA and ADODB connection to SQL server

    I have managed to create some code that takes my SQL (in a cell on my spreadsheet) and it run the SQL on my server. But the SQL I have isn't working. I'm wondering if it's a problem with the difference in languages (JET) etc. I ahve tried both the following but both seem to fail. Cany anyone...
  11. P

    CopyFromRecordset method stops at 65536 records

    I am using ADO to query a table in MS Access 2003, and outputting the data to Excel 2003 worksheets using the CopyFromRecordset method. The table has more than 65536 records, so I cannot use DoCmd.TransferSpreadsheet and need to use VBA with ADO. My problem is that after making a call to...
  12. M

    VBA - ADODB Recordset memory leak issues

    Hi all I really hope someone can help me because I am stuck. I am using 32-bit Excel 2010 on a 64-bit Windows 8 i7 8GB machine. This means I only have <2GB of ram for Excel to play with. I have a workbook that imports data from another workbook using the ADODB.Recordset/Connection model. This...
  13. M

    Help with ADODB connection and Recordset as a class

    I constantly find myself having to write programs in VBA that use SQL for data. I have code where i specify the connection, open it create sql query run it and populate a record set. What i want to do is place that into a class and call it from my various modules so it makes it easier for me to...
  14. A

    Using ADO to add data to fields with similiar name.

    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...
  15. ClimoC

    Disproportionate RecordSet size?

    I have 2 csv's, one 30mb the other 150mb. Once they're recordsets, comparison code begins. Values from the 30mb csv are appended to the extra columns I added to the RS from the 150mb file. It's been running for ages now (a lot of comparisons to do) - and with each passing moment, the RAM being...
  16. B

    VBA ADODB Record Count

    Hi, For some reason the record count for one of my sql-queries return -1. I have the exact same connection and query above, which returns the correct number of records. sqlRS.RecordCount returns the correct number of records, while sqlrs_c.RecordCount returns -1 (but ir really returns 5 to the...
  17. J

    Microsoft.ACE.OLEDB.12.0 Not working

    Hi, I am trying to query some data in an excel sheet using the Micosoft.ACE.OLEDB.12.0 it works fine when there are less than 65536 rows of data, however, when there is more than this it bugs out saying that it cant find the object. Any ideas how to get this to work for large data sets. I am...
  18. H

    ADODB connection and SELECT records from a store worksheet range syntax question

    I am trying to ADODB to obtain a recordset whereby I select records with the following select statement: .Open "SELECT STORE , CASESSTANDARD , CASES9LTR , PCODE , SALE, SDATE FROM " & TableName & " WHERE [STORE] = " & CStr(wsi.Cells(3, 6).Text) & " AND [PCODE] = '" & wsi.Range("A:A").Value &...
  19. S

    Database handling across procedures

    I have some modules which contains some procedures which uses a database connection through ADODB. What is the best way to control all these procedures database connections? Can I somehow only write the username, password and such one time and then reuse this in the other modules?
  20. S

    Output from stored procedures

    I am trying to use some stored procedures in Oracle. I've tried to use the upload-function with objConnection.Execute "BEGIN upload_from_excel(1, 2, 3 ,4); END;", but I don't know how to get any response if the query didn't run successfully. Is there a way to do this in VBA? I've found that I...

Some videos you may like

This Week's Hot Topics