sql server

  1. O

    Questions about usage of SQL Server as Back end and MS Access as Front End

    Dear Friends, I am now so close to finish my Access database (2016) which include both Front end and back end. This will be used by around 200 users. I have 13 table and 13 forms. Current size of the access is 30 MB. I think it will be 1 GB after a year. So I have a plan to use SQL Server as...
  2. M

    How to keep a set of Excel ranges in sync with a set of database tables

    Hi folks, I'm trying to find the best/least-kludgey way to keep a set of Excel ranges in sync with a set of database tables (can be SQL server or MySQL) Here are the requirements: It must be a one way sync, Excel -> database It doesn't have to be an immediate sync, for example a macro...
  3. J

    Excel and SQL Server

    Hello, I'm new to Excel and the database that I use is SQL Server Management Studio. While I have found it incredibly helpful to pull data from my database into Excel, I have not thus far found a way to push data to my database from Excel. While doing some research online, I found an Add-in...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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...
  20. 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...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back