ado

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 & "';" & _...

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

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

Hi, I'm having a bit of trouble getting a recordset to return anything when I run a stored procedure through my VBA. I have tried "SET NOCOUNT ON" and there are results when I have a global temptable. I think this piece of code is where I'm having trouble. Set adoRs = New adoDb.Recordset...

Hi everybody, I'm working on a project and came across a problem. I hope you have the solution. I want to find a selected person name and surname by textbox on a userform in closed workbook sheets. This workbook has 21 sub sheets. Actually, I wrote a code as below but didn't work. I think it...

I'm wondering what others might suggest for a project I'm working on. Quick project summary: I'm working on an Excel Front-End (Spreadsheet, Forms, VBA - the whole thing) with an Access database. The main form has a multipage control with about 35 total comboboxes. My question is related to...

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?

I have an Excel application that uses ADO to query an Access database with linked tables from numerous other Access databases. I've noticed the speed of the queries has slowed down with the number of records increasing each day. This has prompted me to explore optimization ideas where I came...

Hello All, I am having the most wretched time trying to figure this out. I have a worksheet with merged cells (I cant change the input format, I know, I know merged cells are evil and very true for me right now). But anyway in order to get the data I need I have to run 2 queries which works...

I can usually run the following code without issue, but it sometimes fails to find any records in the record set. The file itself is large - over 200k rows. In addition, it has some merged cells, and the column widths are not wide enough to view the data without auto-fitting (I am not sure...

Hi, I want to create Access tables with table names taken from a range of cells in excel. I am not using the VBA variable in SQL statement correctly:(. Please see the FOR NEXT construct in the code below I tried this but doesn't work Sub ADOCreatenewtables() On Error Resume Next Dim cnn As...

Hi All I am trying to port a database kept on a shared file server to a SharePoint Library. I believe that all that is required is a change in the connection string. Example from ConnectionStrings.com...

I have a function that I pass string variables to, to make an ADODB connection to another workbook and run a query against. The variables provide the column and row for the table I am querying, so I am trying to understand how I can read the data from the recordset into an array that will be...

I am looking to retrieve rows from an excel file, format the columns of these rows using SQL, then paste the rows into another excel file. I have been trying to use ADO to do this. Is ADO just for access databases, or would it be beneficial for what I am doing? If it would be beneficial, what...

I am querying a closed workbook using an ADO connection string, which includes HDR=Yes in the extended properties. (The connection is successful.) My query is below: rs.Open "SELECT c FROM [sheet1$B3:L29] WHERE Award = p", Conn, adOpenDynamic Row 3 is where my column headers reside. (Award is...

Hi, Im not sure if this belongs in Excel or Access forum section. I have the following sql code which is working lovely but i need to modify it. sSql = "SELECT * FROM " & i & " WHERE [Pick Status] = 'Ordered'" So obviously it looks at a table and retrieves only records that have a status of...

I am getting "Run-time error '-2147287038'(80030002)" when the code getting to populating the recordset. I can always fall back on embedded SQL, but want to introduce my team to faster techniques. What I thought of trying was to open the connection, the use QueryTable.Add to bypass the...

I've built an ADO function in VBA to handle queries to an Access database. It's setup to process queries with 0 - 2 parameters. I'm trying to expand it to handle an Insert as well. When the Insert SQL and parameter are passed to the function, Excel locks up. Where am I going wrong with...

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

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