ado connection

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?

Hello all, I am pulling the data from closed excel file to my current excel with ADO connection string for excels and I use the following statement to open that query. Everything is working perfectly until someone opens the sourced excel file at the background during that pulling process. If...

Hi. I have recently been upgraded to Office 365 64-bit and I have noticed that the ADO commands for updating data in a SQL Server database are dramatically slower... paticularily in a large (ish) spreadsheet. This simple code example below connects to a SQL Server database and updates a single...

Is it possible to replicate this connection setup on the fly with ADO Connection string in VBA and not have go through a preestablished DSN setup? Connect to Azure Databricks from Excel, Python, or R It's an Azure Databricks connection. DSN is working and I know how to connect using it, just...

Hello there, I have used ADO often to fetch Data from SQL Server and .xlsx Workbooks, but I can't seem to figure out what the problem with my connection string is here. Error message : 'C:\Users\Firstname.Lastname\Desktop\temp.csv' is not a valid Path Sub test() Dim myConn As New...

Hi all, I seemed to have posted this elsewhere but i can't find my post, so sorry if it is a repeat. I have a code that allows me to transfer a few rows (3-16) from excel to access. However when i check the access table it has repeated transferred rows, for example row 3 will be copied 6 times...

Hi All, I was wondering if i could get help with this code below. I use it to transfer data from excel to access, However, if there is only one row to transfer it comes in access multiple times. For example, if i have a row to transfer and i hit the button, the row will be copied five times onto...

Hi Gurus - Thank you for looking into my problem. Below query works when i execute in MS Access. Its a standard query which i plan to call in Excel using ADO. select * from qryForwardRecon; When the above query is called using ADO in Excel VBA , i get zero records. Not sure what could be...

I'm just wondering if anyone knows why I have to execute the query twice in the code below to get the Identity? Example: Set rs = Cn.Execute("SELECT @@Identity as NewID", , adCmdText) x = rs.Fields("NewID").Value '''returns null Set rs = Cn.Execute("SELECT @@Identity as...

The below sub i created to populate a listbox using ADO to read a text file as Access and SQL were unavailable. I needed 2 columns from the txt file and one would differ per user so i used the persons computer username as the column name i would need (so it would work for anybody in the table)...

I have an ADO query that pulls data from other excel files (without opening them - thus, the reason for choosing ADO to begin with), but the queries are erroring out when the file being queried is already opened by another user. Have you seen this or been able to resolve it?

Dear friends, I'm creating an Excel Add-in that has several functions. Also I have a bid data in Access (more than 10 000 rows, and several tables). The Add-in connects to DB when it opens. Then every function in the Add-in use RecordSets to read data from Access DB and do some calculations...

I am creating an Access application that we need to use with SQL Server. I have a some questions that I have not been able to get answers for yet after doing quite a bit of research. I am planning on using a OLEDB connection, However I am a little confused about how to set this up. I have an...

Hi All, My spreadsheet is a front-end for users to execute queries/store procs against SQL server databases and output the rowset(s) to worksheets, this is all working well. It's the error handling I want to improve, currently my error handling consists of the usual "On error" and displays the...

Hey guys, Trying to create a macro button that, when pressed, populates empty columns, based on a match between Part_ID in excel sheet called BOM and Part_ID in database table called materials joined with tables manufacturers and vendors The code is as follows: <code style="font-family...

I'm not too familiar with Access and linking to it from Excel so thought that on a recent project I would try and build up my skill set and use ADO, plus the use of a database in this instance was appropriate. So far, I have figured out how to Recall a record that matches a certain condition...

Hi - I am creating a custom ribbon in Excel to perform a variety of analysis on a dataset that is refreshed everyday. The first button I have included on the ribbon is one that I would like to import data into an Excel worksheet from an Access table. I have the following module, however, I am...

Hi, I am working on a procedure and just want to know if 2 ado connections are possible at once like this because I am getting an error that says "Object doesn't support this property or method" and the highlighted code is causing it but I know the field exists. Thanks! Dim row As Integer Dim...

Sub ConnectDB() Dim sFileName As String sFileName = ActiveWorkbook.FullName Set cnn = New ADODB.Connection cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFileName _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" End Sub Sub ImportPri ....... Call ConnectDB...

Hi I will soon have a excel sheet distributed between hundreds of users. When the excel is first opened, it gets connected to a mySQL database (and stays connected until excel application is closed) People then do queries, etc using the following code. ' connects to database when workbook is...