1. D

    SQL in VBA Excel to query multiple tables in Access database

    Hi guys, I have an Access database with the following tables - Clients, IT2019, IT2018, IT2017, IT2016 etc. A client can exist in multiple IT... tables, or just one (depending on when a client joined). All tables use the field ClientCode to identify clients (This is the primary key in all...
  2. J

    Reference Libraries Disappear at Startup

    I have to enable the following reference library to execute SQL commands in VBA. While I have added my UDF into my startup workbook, the reference library becomes unchecked every time I open and close Excel? How do I get it to stay added whenever I open my startup workbook? Libary: Microsoft...
  3. S

    Unable to add new line with "Enter" in Table Import Wizard (Data Model)

    Whenever I press Enter to start a new line it returns me back to Edit Table Properties. Is it a normal thing ? Is there a way to use Enter as a new line command ? na
  4. J

    Type Id and search record directly in sql

    Hi there to all Mr Excelssss men. Ok, i know we can type an sql query and bring data to our excel. What a want to do, is to type something, which in this case is an Invoice ID number, and that triggers sql query and give me back some fields. I actually have the query that brings all the records...
  5. C

    Where in SQL

    Was wondering how I can use a Where statement in a SQL qry Am doing a mail merge, and trying to avoid the rows in the Name column that only contain a function that returns "" SQLStatement:="SELECT * FROM `" & wsName & "$`", SQLStatement1:=""
  6. J

    VBA and SQL

    I have to insert some data into an .mdb file from an excel worksheet. The ADO connection was created successfully. The 'insert into' query has to be combined with 'where not exists' to avoid inserting duplicate data. I have to select some data from a worksheet of the active workbook (say 'c') to...
  7. S

    Use VBA to generate SQL reports

    Hi I need help in writing a vba code which would connect to SQL and run a SQL statement to generate a report based on company name. The code should then save the output to the defined location in the excel data table The data table in excel has company names from b2:b50. Column C is a user...
  8. A

    VBA grammar for sum of two fields in SQL table

    I have an SQL statement that sums up two fields and is working find in SQL Select w.Forecasted_hours + w.actual_hours as 'TotalForecast' however in VBA I'm not exactly sure what would be the right way to do it. If I paste the SQL it seem like not recognizing a plus sign. Can anybody please...
  9. gheyman

    Access: Design view problem - only showing SQL

    I am trying to see my query in Design View, but its only coming up in SQL (I am clicking Design View not SQL View). I'm not that good at SQL so I need to see it in Design view. Anyone know how to fix this? Thanks
  10. A

    VBA - too many line continuations

    I have a very long SQL statement which I'm trying to put into my VBA code. In my VBA I have the following format for SQL: With ActiveWorkbook.Connections ("Database").OLEDBConnection .CommandTest = Array ("Select * bla bla bla") I looked through some web information and it was suggested...
  11. A

    SQL statement VBA : need to ignore blank values

    Hello, I have a VBA with an SQL statement. I need to ignore any values that are blank Below is portion of VBA: "SELECT * FROM Data.dbo.Contract_Summary_Workbook as w WHERE w.""ProjectNo"" " & _ "like '" & Lists.Range("C4").Value & "%' or w.""ProjectNo"" like '" &...
  12. A

    Ways to bring data from SQL server but need input parameters for SQL to read

    I was wondering if someone can please help me on best way to use parametirized SQL query and bring it to Power Query. I have access to SQL server with known connection string. SQL is long and needs to use specific input parameters (project numbers) in it. I don't want to have them changed each...
  13. S

    Scheduling Possibilities?

    Hello, I have Power BI Pro installed as part of our E5 license and I am trying to evaluate it in thoughts of using it instead of our current IBM Cognos setup (that we pay extra for) fo rquerying our SQL server tables... However, the one concern I have that I do not think Power BI can do - but...
  14. B

    Concatenate Records from Record Set (rs) SQL Query from ACCESS Database

    Hi Cummunity, Needing some help to concatenate row values being retrieved from ACCESS Database. INstead of writing to multiple rows, i need to concatenate all the record set values and write it to a single cell. Here is a view of part of my code: Currently there are two rows in the database...
  15. D

    SQL Help - **Newbie**

    Hi ya'll - realizing this isn't an Excel question but hoping someone could help me interpret the output of the following SQL query and how it is structured. Here is some information about the database: - conversations_conversation records represent a single conversation between two people...
  16. vijnanamatrata

    Recreating NTILE function on running SQL in Excel

    I am making a conversion from traditional way of creating reports in Excel, and I have been stuck on recreating NTILE function when running SQL in Excel (because NTILE function does not exist there), even though I have succeeded in recreating NTILE function when running in Oracle PL/SQL...
  17. B

    SQL Insert Statement > Start Insert Records from Row(3) and onwards

    Hi Community, I need some help running a SQL statement to insert rows starting at Row #3 . The statement works and will insert records, but I can't get it to start inserting at Row #3 which is messing up the data that's being put into the Access Database. Any guidance? Public Sub ShiftSwap()...
  18. gheyman

    Change Query Sql with VBA

    This is all new to me. I am reading posts and trying to piece together how you change the Sql code of a query using VBA. Here is what I did - but it does not work. In my code the Line that starts with WHERE (((.... turned Red. When I try to run it, I get a message that reads: Compile Error...
  19. D

    SQL Linked table

    Hi All, I am new to SQL but learning quickly. I have a spread sheet that has on tab linked to an external SQL database an several other tabs with static data When I run the following "select * from linked" (this I the linked sql data) I get no results, however if I do "select * from...
  20. bs0d

    ADO SQL Query - Multi Users, Schema Name Required?

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

Some videos you may like

This Week's Hot Topics