adodb

Hello everyone I am trying to read data from a closed workbook. I would like to use dynamic range names. I define these with the name manager. My problem is as follows: If I set the name to fixed =Sheet1!$A$1:$A$3, then access to the datarange works perfectly. However, if I create the name...

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

I am trying to connect to Microsoft Access using ADODB.Recordset in VBA, but apparently it seems to only accept alphanumeric table name. Changing table name manually in Access is just additional work (and I need to teach it) for the macro user, so it would be great if it can work without doing...

I am using excel Userform and MS Server. I have a Userform (OrderFrm) that I populate 2 multi column combo boxes with lists from my database (Customers & Items) there is also text box for inputting quantity and a text box that is populated with Price on a change event of the Items combo box. I...

Dear All, Especially the ones more familiar with M code, databases and data analysis. I admit that PowerQuery and M code seem to be quite a versatile tool, but I am puzzled by its low performance. So I wonder - is it me doing something wrong or it's just the way it is. Is there anything I can do...

Hello, I've been trying to fix this all day with no luck. The goal of this code is to use a function to look up a value in a table in an Access database (.accdb) and return a different value. I am running the code below, and the adoCN works just fine. It creates a locked .accdb file showing...

I need to write to multiple tables in my MySql database from excel (possibly 20 plus). I will be needing to use PK of certain tables i update as FK of others. I would really like to use Transactions to insure all information is update and if something errors out for any reason then i can...

What is the difference between an ACCDB database and a ADODB database. I use Access 2016. My current database is ACCDB. How can I convert it t ADODB.

Hello everyone: I'm trying to delete all records in a RecordSet ADODB Object connected to a Excel Sheet, using two differents ways: Delete records using an ADODB Connection Object Delete records using an ADODB RecordSet Object Also, as far as I know MS recommend to use the 2nd So, what...

Experimenting with queries to my worksheet. So far, I can successfully get meaningful results back, but wonder how I can get those results to include the row they were found in?? My query Looks something like "SELECT [test1], [test2] FROM [Sheet1$]" Want query to get row some how in results...

I have a rather large query, containing a table join that returns approximately 80k rows; executing this query via excel VBA seems to result in a minute or longer of processing. I don’t have the same speed issues when returning 5-10000 rows in excel; just the one that’s returning 80k rows. I...

I am trying to find data from access using the below code. The variable R is the date which it will search in the table. If date find in table, it will pickup data from some of the field else it will close the connection and loop will forward. Now the statement "On Error GoTo ST2" is working for...

Hi, I am completely stuck on this one, any help will be appreciated. I have a code which is supposed to do inserts/updates in database. I have connected to database using both (one commented out): Sub SQLConnection() Dim oConn As ADODB.Connection Set oConn = New ADODB.Connection...

I am able to successfully connect and query specific sheets. Some files I am dealing with may have the first spreadsheet name different or changing and sometimes there may be more than one. i tried to write a few different functions to return a full list. However none give me the spreadsheet...

Hello there, I'm having a problem with inputting dates via SQL commands to my access database as they are getting changed to peculiar values. Background: The tool is a counter for the amount of work a person has done, each time the button is clicked the updated values are sent over to the...

Hi all, I need help troubleshooting this code. When I run it, it errors out at the .Update saying [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression '(1stMedication=Pa_RaM011 AND 2ndmedication IS NULL AND Qualifiers IS NULL AND Route=Pa_RaM012..." How do I fix...

Hi all, I have developed a rather large application with VBA that we use at work. The problem with this is that the database is now in the excel application, and that means that no more than one person can use the application at a time. This is normally ok, but I thought I would try to fix this...

I have written a complicated SQL script which runs fine, except for some reason a date column is being returned to Excel as text, rather than as a date. The field is a date, and I've even tried CASTing the field to date in the SQL just in case, but still get the same problem when the recordset...

Good afternoon, First off, thank you all so much for all the help you have provided my lurking self throughout my history of learning excel and vba. On to the question: I am using the following code to compile a bunch of daily inventory sheets into one master sheet. It functions perfectly...

I am trying to pass a recordset to an HTML javascript. The reason is that my company's web page sercurity policy does not allow me to create an "ActiveXObject" in javascript. I was experimenting with passing a recordset from VBA to HTML javascript function as a work around. I can't seem to...