sql server

  1. J

    VBA/DAO: Inserting MANY rows of data from a range into a SQL Server table

    I know -- ADO is much better for many reasons, but for various political reasons I have to use DAO. Anyway, I've written VBA/DAO code which inserts from a range to a SQL Server table one row at a time. It's cumbersome, because I have to slap together an INSERT INTO table (columns) VALUES...
  2. S

    Trying to get the Identity right after .Update using SQLOLEDB

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

    Getting data into Excel from SQL Server using Microsoft Query

    Here's a query that works just fine on my SQL Server Studio. select t.converted_date as AsOfDate, a.acc_name as Name, mvs_mkt as Prt, a.field_7 as AcctType from IndataDB_MarketValues..tblMarketValues m, IndataDB_MarketValues..tblDateTable t, indatadb_main..rimsacc_Master a where m.mvs_counter...
  4. Err

    Linked Tables performance issues: is a pass through query the only option?

    Hi, I've inherited an Access app that allows users to run a custom query using a form. It -has more than one linked table from SQL Server -Runs Access Sql on the tables before presenting The problem is that the tables were relatively small when they were made by my predecessors. Now they are...
  5. S

    POWER QUERY SQL SERVER statement error

    Hi I need help converting a MS access sql statement. I ran it throuhg an online converter and everything has been fine. But I've been told to speed up the data selection I should put into my sql server query the date filter. But when I try it now says the query is invalid based on the last line...
  6. S

    Using SQL Query problem

    Hi I've written the statement below. But it's complaining about the "HAVING" part. I'm trying to prefilter the data prior to using Power Query. SELECT tbljournal.posted_dt AS [JOURNAL DATE], tblreceive.link_journal, tblreceive.dest_loc_code, tblreceive.reference_no...
  7. S

    Using VBA how to change the backend query definition of a table in Excel

    Hello Dear All, There is a table in excel 2016 made out of a SQL Query which is linked to a few pivot tables, each month we open the SQL query in Query Editor and change the parameters and save. Later we refresh all the pivot tables to update. My Question is: Is there a way to change or update...
  8. K

    Show blank combinations in Pivot Table based on MS SQL

    Hi, I am using a relational star scheme with fact and dimension tables (facts containing sales, product table contains product details etc.). Based on that I would like to use a pivot table that shows all products in rows or columns even if there is no record in the underlying fact table for...
  9. T

    Data Connections not Saving in Workbook -- HELP

    I've created a workbook with a simple data extraction from a SQL server that loads to a Table. It works great on my computer, but when I try to distribute it to other people, the Table's connection to the SQL Server query is lost, so they are unable to update the data in the table. The...
  10. H

    Which Data Import Method to Use in this Scenario ?

    Hello I have got a few Excel Files in which I need to import the data from a few different queries from a database in SQL Server installed on my local machine. Then I intend to use this imported data inside Power Pivot Data Model. I am not sure which particular data import method should be...
  11. J

    Question You are subscribed to this thread Applies to Office Office 2016 Microsoft Office Excel Office for Windows 10 Desktop 4 views SQL Server odbc

    There are several ways to set up ODBC drivers to SQL Server in Excel 2016. There is one method that is cross-platform Mac/PC but this method is not working for me. One way that does work is from the Data tab of the Ribbon choose Connections. I can set up a connection to my SQL Server that works...
  12. K

    Excel (PowerPivot) vs MS Access vs SQL Server

    I've been searching the web high and low to help me explain, compare, and differentiate between data manipulation using various Microsoft Office tools. I've found many links and articles pre-PowerPivot and PowerView days, but cannot find anything more current. Please help me understand the...
  13. E

    Link SQL Server Azure to On Premise SQL Server

    I am building a Access Web app in office 365 (something I have never done before). I think by default the tables created in the app are stored in sql server azure. Can I specify or define the names of the sql server azure and link to our on-site sql server? I am trying to sync the data tables...
  14. R

    Excel Vba handling errors from Ado Connections/permissions to SQL server.

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

    Is thorough SQL server knowledge required for Business Intelligence career??

    Hi All, Happy New Year to all of you.. I am an Excel Power user, Excel trainer, BI enthusiast slowly getting into teaching Business Intelligence using Microsoft Excel's PowerPivot. A few years back I was bitten by a bug called Power-BI. I am not a person too good at programming languages. But...
  16. D

    error when adding a single row of record using iteration within a range

    Hi there, I am connecting Excel 2013 to SQL Server 2012, trying to adding a sheet of data to a corresponding table in SQL Server. The code looks like this Dim MoviesConn As ADODB.connection Dim MoviesData As ADODB.Recordset Dim r As Range Set MoviesConn = New ADODB.connection Set...
  17. J

    Connecting to SQL Server database

    I have a VBA function called GetBalance(parm1,parm2,parm3,parm4,parm5) that is used to connect to a SQL Server database, execute a query, and return a dollar amount, based on the parameters (type of account, fiscal year and period, balance type, etc.) The function allows users to build very...
  18. S

    Macro / VBA to update connection string to different SQL Server

    Hey all, we have a whole bunch of sql server instances that have the same database and table, id like to be able to update my excel spreadsheet which connects to it via a macro instead of manually going in and changing the server each time. its windows auth all the way, if that helps simply...
  19. bs0d

    Query Speed - Linked SQL Tables

    I have a general question. Suppose you link SQL Server tables in an MS Access Database. If you write a query from another application (like Excel) to the MS Access database, how much slower would that query be vs. the same query directly to the table(s) on the SQL Server?
  20. R

    Odbc/sql server access in Excel - repeatedly asking for password

    Hi, I have created a macro which creates a SQL Server connection and returns data as requested - this works fine, however, my macro involves running the same code a number of times - also works fine, just with different connections - still all working! However, because I am making a number of...

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top