1. bfreescott

    ADO over VPN

    Whether we're talking about ADO or DAO, the best use case for me has been querying data sets, some of which are Excel files. As long as the data source exists on the same network, the speed of ADO over an ACE connection far outperforms a approach. I've noticed however that when...
  2. tjdrake

    Multi-User Access DB Table

    Hello Team, I've been struggling with this all week, and am running up against a deadline to make this work for my team. Here are the essentials: The MS 2021 Access DB sits on a SharePoint Server; The SharePoint permissions have been granted to Everyone; The code updates with a new record...
  3. O

    ADO method or Power Query to pull the data from other closed excel files

    Hello all, Which one is faster and better to implement when we want to pull the data from closed excel sources to our existing excel. What are your opinions?
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. D

    Vba- ado connection

    I want to learn ADO connections. I want to copy data between closed files. Where can I start to learn?
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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...
  20. 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

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