sql server

I'm rolling out an Excel/VBA frontend that will use an SQL Server backend. I am going to have both Mac and Windows clients, so I'm unable to use ADO and recordsets. My research found two libraries that will work with VBA and let the Mac execute SQL statements against my database. * Openlinks •...

I'm using Excel Professional Plus 2016 to connect to an external SQL Server connection. First, I pull the data into the Excel sheet and now see the data that exists based on my query criteria. [Shown as columns A,B,C] After that, I'm adding new manual columns that do not exist in the external...

I am using VBA to insert 6-column data from Excel to SQL server. The data will be firstly convert to JSON, then to DB. If the rows are less than 2600 rows, it will be inserted. Otherwise, VBA reports the data sent, but it's actually not in the DB server. The biggest column data length is about...

Hi - I am sure I must be missing something very simple here as I can't believe the trouble I'm having. I have a SQL Server database with a load of tables and data, pretty standard stuff. I have had no problem connecting to this data and pulling it into the PowerPivot data model. Now I have the...

Hi, I am trying to copy one table's records into a variable named table as below, Here Source Table is 'Samples' and wants to get all its records into '@testing' Table. But it gives an error 'Incorrect syntax near '@testing' I'm on Sql server 2014. Any help would be appreciated. Thanks.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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