1. B

    VBA to Populate Excel Userform Treeview from MS SQL

    I can not seem to figure this out, can someone assist please. I have a userfrom in excel that has a treeview, I am trying to populate the treeview with a recordset from ms sql using ADO. I have managed to populate the Parent Nodes but have not been able to figure out how to populate the child...
  2. B

    VBA and sql stored procedure

    I have a stored procedure in sql that I am calling from vba in excel (using ADO). The stored procedure Inserts a record into the db table with 7 parameters. The worksheet has 7 columns. There could be 100 or more rows at one time on the worksheet that I want to insert to the sql db using the...
  3. J

    ADO Query to Check List from another List

    I am trying to filter my Excel table records based on a field which is composed of list separated by a comma. I tried to use a public UDF on my Excel module that returns a boolean to add a condition to my SQL query but I found out that it is not possible. I've got runtime error Undefined...
  4. M

    How to keep a set of Excel ranges in sync with a set of database tables

    Hi folks, I'm trying to find the best/least-kludgey way to keep a set of Excel ranges in sync with a set of database tables (can be SQL server or MySQL) Here are the requirements: It must be a one way sync, Excel -> database It doesn't have to be an immediate sync, for example a macro...
  5. bs0d

    ADO SQL Query - Multi Users, Schema Name Required?

    I created an Excel workbook that uses ADO query a database on our SQL server: Set myConn = New ADODB.Connection Set myRS = New ADODB.Recordset strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=db_server_name;INITIAL CATALOG=database_name;INTEGRATED SECURITY=sspi;" myConn.Open strConn...
  6. M

    Syntax error in UPDATE statement using ADO

    I've looked everywhere for a solution to this issue with no luck. I'm using ADO to connect a workbook to a second workbook and then update certain fields in the second workbook. I can get this to work perfectly on any data type except a date. The second workbook has a column called Timestamp...
  7. B

    ADO Events and Data Integrity

    Hi, I have built a userform in excel which stores all of its data in a access database. There are multiple users at once but only one connection to the database is allowed to ensure one table is not updated while another user is using it for something else. I am wondering if ADO and access can...
  8. N

    VBA ADO and Azure Sql server

    Hello, first I want to say that, I'm very new with Azure and Sql server. second, I have an VBA ADO code with a simple select query from an Azure SQL SERVER db. On my main PC (the PC with my SSMS that i use to contacat the sql server) the code works perfect, but on any other PC (I used the same...
  9. D

    Vba- ado connection

    I want to learn ADO connections. I want to copy data between closed files. Where can I start to learn?
  10. dageci

    VBA & ADO: INSERT INTO Blank worksheet, Field Types

    Hello, do you have any tips on how do you insert a record into a BLANK worksheet using SQL INSERT INTO and that the columns are recognized properly? For example, if we have a worksheet with 1 Number field, 1 date field, and 1 string field, and if we use the INSERT INTO when the worksheet in...
  11. K

    Formula not being evaluated in COUNTIFS multiple criteria array

    I'm using the following formula: =IFERROR(IF(SUM(COUNTIFS(INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),2))):INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),17))),{"Full...
  12. B

    Executing a VBA function in a ADODB SQL query on Access from Excel

    So I use excel as my main application and use ADO to connect to an access database. Well something I wanted to accomplish which was concatenating rows in a database field is not possible without a vba function. Well I was wondering, if I save a module in the access database with the function in...
  13. E

    Insert Excel Sheet Data into Teradata database USING ADO IN VBA

    I have vb code using ADO that can insert a single row of data from an Excel sheet one at a time. But I am looking to insert the entire contents on the sheet1 all at once. Here is my single row code. Sub SampleInsertLoopingThroughRows() Dim conn As ADODB.Connection Set conn = New ADODB.Connection...
  14. B

    ADO WHERE question

    Hi All, Trying to learn about ADO to fix my big file issues seeing as work wont let me use an SQL DB , R or Python. Can anyone tell me whats wrong with the below WHERE condition? I'm debugging at mrs.Open with an error of "Parameters expected 1" Conn.Open sconnect sSQLSting = "SELECT * From...
  15. B

    What happens from pressing a VBA userform button twice?

    So I am trying to understand what exactly happens if you press a VBA command button twice if it runs a subroutine. For instance does the first execution of the subroutine run by the button stop mid code and restart since the button was pressed again? I am trying to set up some ADO connection...
  16. G

    VBA - ADODB connect to remote Oracle db - "Runtime error '-2147418113 (8000ffff)': Catastrophic failure"

    Hi all, any help very much appreciated, pulling my hair out here!! I am developing an Excel VBA program that I want to connect to a remote Oracle 11g database, run a query, and return the data to Excel. Connection strings and drivers are all OK as far as I know. (see below) It was working...
  17. M

    Connection to a database used globally

    Hi, I have a Excel VBA project which use a database server as a backend. My problem is that I have to use the connection string, ADO and Recordset in every procedures. So, I want to ask You if is any solutions to use the connections (string, ado, recordset) string once for all procedures. Thanks
  18. S

    ADO Connection to Excel : Excel 2010

    I recently upgraded my computer to a 64 bit computer, however, Excel (2010) on my computer is 32 bit. I have the following ADO connection that worked fine on my old computer, but now gives me a 3706 run time error: conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & strDir1 & "';" & _...
  19. B

    ADO Status indicator

    Hi, I have a vb script on various users computers which links to an excel workbook as read only. On open this workbook creates an ado recordset and connection object. Through various tools inside the workbook the macros will connect to an access database and read or write to it and then close...
  20. bfreescott

    Where are the ADO experts?

    Hello ADO experts! I love using ADO to query Excel files because I can forgo Workbook.Open to access the data in the workbook. Much faster execution. The seemingly major flaw in the ADO design is that you must refer to the sheet name explicitly. If I don't know the sheet name, what do I do...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back