sql

I am trying to run an SQL query via VBA and seem to be running into an issue with the length of my query. The query is connecting to a SQL Server table, and is to select particular fields from the table. The length of the string with the code is ~2100 chars (the field list is built via a loop...

Goal: Search a dynamic list of items to plug into a sales order query from a cell reference. I know how to reference a cell reference for only one item that can change in a cell reference, but I now need to know how to reference a cell or cells that have multiple items and filter to just those...

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

Hi All I am new to Excel /VBA and have been asked to create a excel workbook that takes data from a SQL table and the user then updates the spreadsheet and then clicks a button to update the table. I seem to be unable to get the update to work and to me its a very simple task! (Or Should be)...

So I am looking at COVID stats all day and I have to prepare them for my manager. This is a multistage process and it's fairly repetive. As a result i want to automate it so i can spend my days doing more productive **** or cat videos. Either i am not bothered. It's a multi stage process and...

Hi all I have downloaded the next file. It has a userform that makes a dropdown list for each column and it filters the results (Which i belive it's very cool) The problem comes when i perform some changes to the database, in where i put some values for the last column, as you can see in the...

I need to connect to CSV file in my downloads folder using SQL query from Excel VBA. This is the code I found from internet Sub SQLconnectCSV() Dim xlcon As ADODB.Connection Dim xlrs As ADODB.Recordset Set xlcon = New ADODB.Connection Set xlrs = New ADODB.Recordset...

Hi, Tell me please how SQL database queries generated from MS Excel and loaded directly into Power Query works (via Data> Get Data> From Database> From SQL Server Database). Does the query generated this way run slower and overload the database more than if I wrote a SQL query by hand? Let's...

Hi, I’m using a simple notepad .vbs macro to refresh all spreadsheets in the folder which is scheduled to refresh twice a day by task scheduler. The data source of the report is SQL server and hence I’ll have to leave OLE DB refresh option enabled to make sure the data remains up-to-date. When a...

Hello all, Is there a way to remove the header row on my output in a sql query? I just having to manually delete it. Thanks

hello all, I have this functioning SQL code: SELECT LOANMAST.BANK, LOANMAST.BRANCH, LOANMAST.LOAN_NBR, USERF.USER13A, LOANMAST.POSTDATE, LOANMAST.LOAN_AMT, LOANMAST.SHORT_NAME --USER13A must say "D" in all rows FROM LOANMAST LOANMAST LEFT OUTER JOIN BORRLIST BORRLIST ON...

Guys I have the following problem. I need to identify if the date of the record is the earliest date of the group. The idea is NOT return an aggregation table, the idea is return all the records but in a custom column I want to identify if the date of the record is the earliest of the group. If...

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

Hello all, I'm looking to enter the below query from a sql server database. However, in the Enter Connotes section, rather than entering in a list of connotes into the code, I would like for it to refer to a list of connotes in the excel workbook. I would appreciate it if someone could advise...

Hello all, I found what I thought was a working SQL solution, but I am still having errors. I get run time error saying server does not exist or access denied. Sub sqlmaybe() 'Initializes variables Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim ConnectionString As String...

Dear friends, I am struggling for creating a ODBC data source and then link my table from SQL server. I will tell step by step what I did so far. 1- I have completed MS Access as a compact application (BE+FE) Therefore, it is not available to be used. 2- I requested my IT department of my...

Morning all, I have a question regarding SQL queries when creating a data connection. I have created a data connection to an external workbook to a sheet that has a table in it. This table starts at row 13 (the headers) and is a dynamic table that gets updated from time to time. In my query...

I have a database spreadsheet where I need to pull key information from the master data. As you can see in the below screenshots, the goal is to return the "Part Number" and the only data I can match with are the "Kenn" and the "Part Code", "Kenn" can be directly matched with "KENN No" from the...

I have the 2 tables below: Table1 ID Start Date End Date ABC123 01/04/2020 13/09/2020 DEF456 03/01/2020 17/02/2020 ABC123 15/09/2020 30/09/2020 Table2 ID Start Date End Date ABC123 17/12/2019 12/07/2020 ABC123 13/07/2020 11/11/2020 DEF456 07/07/2020 09/09/2020 DEF456...

Using the code below, everything works well until one of the CompanyName values have an apostrophe in it. VBA generates an error. For Example: CompanyName = "John's Plumbing Supplies" How can I modify the code to be able to update the CompanyName Field in Access when the string has an...