ado

I currently have an issue trying to open PW protected file. My question is this: "Has anyone successfully opened either and XLS or XLSM file with ADO?" AND not had to actually open the workbook up in code first? Everything runs fine without a PW. I've tried moving the password location in...

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 WORKBOOK.open approach. I've noticed however that when...

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

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?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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