adodb

Good evening all I have an ADODB connection to a worksheet within the same spreadsheet - its a dataset that I wish to query using SQL in order to run reports. I'm doing fine except some gaps in my knowledge of syntax for this type of query Subs to open and close the connection: Sub...

Suppose I have a table in Excel with two columns (Name, Rate) (say, this table's name tExcel). Table starts at cell (2,1), and Date is static (in cell (1,1)) I want to insert those values into SQL Server 2008 tRate table with following logic insert tRate(ID, Rate, Date) select s.ObjectID...

I regularly run queries inside excel VBA. What I do is I store the SQL code in a String variable 'sqlStr', and then I use that string and pass it to a function which runs SQL Code by opening a connection with ADODB, connecting to a 'serverName' and 'databaseName'... there are hundreds of...

Hi, I am not sure if this is an Excel Question or Access Question, maybe neither. I have code in vba that extract data from accdb file using adodb connection. For some reason when i take this code to another computer where it doesn't have access installed, it got a system error &h00400etc. Has...

I have an SQL Query which returns a subset of data from one worksheet to another worksheet. I was hoping that instead of returning the data to a worksheet I might be able to return the data to an array instead. Here is my query: Dim sel As New cls_Selected Sub Data_Ext_From_Excel()...

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=" &...

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

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

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

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

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

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

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

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

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

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

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

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

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

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