Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

ODBC Connection and SQL Query

Hi All,

I would like to make a VBA macro in Excel, but I'm new in this theme.

My macro have to connect a database with ODBC where is five relation tables, and I want to print the results of the SQL queries in the Excel.

How can I do an SQL query in VBA, how can I connect to ODBC?

If somebody can show me a good documentation about this theme, it's enough.

Thanks.


Post your answer or comment

comments powered by Disqus
ODBC Connected Table (SQL Server 2008) Orders Incorrectly

PROBLEM:  From Access 2007 I query the ODBC-linked SQL Server 2008 table "allocation", joining it with the local 1-row table "curent" using the following query:

SELECT allocation.LN, allocation.OCA, allocation.CD, allocation.DESCRIPTION AS [FUND DESCRIPTION]
    , allocation.ALLOCATION, allocation.NONMACSIS
    , allocation.BLEND, allocation.GOAL, allocation.EARNED, allocation.SHORTAGE
FROM allocation INNER JOIN [current] ON allocation.PAYEEFK = current.PAYEE
ORDER BY LN

The records appear not in ascending LN order, but in what looks like the creation order of the records.

ADDED INFORMATION: The "PAYEE" field in the table "current" is a unique index.
The SQL Server table, "allocation" has a clusterd primary key made up of the small integers "PAYEEFK", and "LN".
When I run a similar query on the SQL server in Management Studio there is no such problem.

The SQL Server runs on Windows Server 2008 and the Access front-end runs on Windows XP
WORK AROUND: Wlith the expression LN+1 substituted for LN in the ORDER BY clause, the query orders correctly.

The work around is quite satisfactory, but I would like to understand the problem better so I can head off future difficulties.

Thanks,

Harry

I have a project where I pull data from a database to refresh a query in each of a set of worksheets within a set of workbooks. (EX: For each county in Tennessee, there will be a workbook created and for each of these county-workbooks, there are 7 worksheet-queries to be refreshed.) Which county it is is determined by a "master" or "controlling" worksheet that includes a row for each of the counties. This worksheet-array is "walked", with a new workbook opened_as_template AND/OR saved_as_county_report_date_name ; that is, each county will have its own workbook file name.

I do this by assigning the value of the current cell (CountyFIPS, the alphanumeric ID for each county) to a variable. That variable is used to construct the file_path_string for saving each file. This variable - vCountyFIPS in the snippet below - is also passed to the module that SHOULD refresh the SQL query :


	VB:
	
 cmd 
    .CommandText = _ 
    "SELECT ountyFIPS,RPT_COL_1,RPT_COL_2 " & _ 
    "FROM REPORT_DATA_TBL " & _ 
    "WHERE CountyFIPS = ' " & vCountyFIPS & " ' " 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've learned enough Excel_VBA to manage most of this okay, with help fm this site, others, and a couple of books.

However, what I'd like to know is if I have to re-define the ODBC Connection and/or the query. It seems to me that I can define a template-workbook with each of the 7 queries, use the control workbook (the County data) to establish the ODBC connection, open the template-workbook, save it with its county-specific name, refresh the query based on the vCountyFIPS variable, then save the file, and move to the next county.

Any tips, advice, code that might help me work thru this?

Thanks,
Steve in Memphis

I have an Excel file connecting to an Access database using ADO. The SQL statement for the recordset connection was working just fine until I added additional criteria to my SQL string. Because it failed, I copied the SQL string directly into Access and tried to execute the query. It timed out the first time because the default ODBC timeout for a new Access query is 60 seconds. I increased this interval to 120 and reran the query - it worked perfectly. I think the code may be timing out in when the query is executed through Excel, but I've tried everything that I can think of and nothing is working (ie application.odbctimeout=0, cnwarehouse.commandtimeout = 300, cnwarehouse.connectiontimeout=300, etc). The error I am receiving in Excel is:

Run time error '-2147467259 (80004005)':
ODBC--call failed.

The error is occurring on the last line of code (opening the recordset).
The error started when I added " And Data.Ledger_Amount >= 123". I would greatly appreciate any suggestions. I will post my entire connection and SQL string code below for reference. Sorry - it's kind of long :-) Thanks.

cj


	VB:
	
Set cnWarehouse = New ADODB.Connection 
cnWarehouse.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; " & _ 
"Data source = C:Documents and SettingsMy DocumentsCessna InfoWarehouseWarehouse.mdb; Jet OLEDB:Database" 
cnWarehouse.CursorLocation = adUseClient cnWarehouse.Open 
 
strSQL = "SELECT Data.Mgmt3, Data.CCID, Data.[CCID Description], Data.Category, Data.[Category Description], Data.[Detail
Account], Data.[Detail Account Description], Data.Financial_Month, Data.Trans_Date, Data.JV_Reference, Data.JV_DESC,
Data.JV_PREP_BY, Data.Description1, Data.PO_Num, Data.AP_Record_Type, Data.Invoice_Number, Data.Control_Group,
Data.Vendor_Code, Data.Vendor_Name, Data.Ledger_Amount FROM Data WHERE 
Data.Mgmt3 = 'Finance' AND Data.CCID  '84800' And Data.CCID  '41800' AND Data.Financial_Month >= #5/7/2006# And
Data.Financial_Month = 123
 
 'open warehouse recordset
Set rsWarehouse = New ADODB.Recordset 
rsWarehouse.Open strSQL, cnWarehouse, adOpenStatic, adLockOptimistic 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi. Can someone tell me how to use VBA to set the ODBC connection string for queries?

My workbook has 9 queries, all needing the same string. I would like to have a msgbox pop up, let them enter the directory name, and have that change all the odbc conneciton strings automatically. Can anyone point me in the right direction?

The message box would be a nice touch, but is not necessary, I can change it in the VBA editor if needed.

Currently, they connect using a Visual FoxPro ODBC driver. We setup the connection when we made the queries. Now that they are in on the worksheets, I want to be able to edit them with VBA instead of using the script editor (which is very slow).

Thanks!

Note, I screwed up my original question and I apologize for cross-posting. My original question was posted under the title: Connection Strings in ODBC - lockable? -- note I would still like to know if they are lockable/protectable, but that was a secondary issue... Sorry!

I have Pivot tables and SQL Query in many sheets.

When I open the file I have to manually refresh by right-click and press refresh.

How do I coding VBA to refresh all Pivot Tables and All SQL Query in all sheets of the file.

Thanks

Hi,
I am trying to establish connection with a SQL Server database (for which I already have a ODBC connection setup), and execute a query, and use the output of the query to do some calculations.

I tried the following, but could not get it working. I know I am missing some key commands.... Do I have to use RecordSet ??

Sub DatabaseConnect()

Dim Conn As Connection
Dim Rate As Double
Dim Sql As String
Dim Confirm As String

Confirm = InputBox("Enter the Confirmation Number", "Input Parameters", " ")

Conn.Open ("Driver={SQL Server};" & _
"Server=IRIS;" & _
"Database=RLink;" & _
"Uid=Admin;" & _
"Pwd=1234123")

Sql = "select ItemRate as Rate, ForeignAmt_NDec as SettlementAmt_NDec, ForeignAmount as ForeignAmount, * from rueschlink.dbo.OrderDetail where ClientOrder_ID = ( Select ClientOrder_ID from rueschlink.dbo.ClientOrder where ConfirmationNo = ' " & Confirm & " ' )"

End Sub

Parameters in BOLD from above are the outputs that I am looking to get from the database to use for further calculations later on.

Any help will be greatly appreciated.

Thanks.

Programmers,

Here is the situation: The company I work for has several Excel spreadsheets that are linked to our SQL Server 2000 database via an Access file. All has worked for years until now. When I try to Refresh the spreadsheet for Company3, I get an ODBC Connection Failed Error. Basically, the configuration is as follows:

SQL Server: Database has 3 tables for each company which we will call respectively - Company1, Company2, Company3.

AccessFile.mdb contains linked tables to those tables.

Excel Spreadsheets have a Microsoft Query defined using a Microsoft Access ODBC driver. From there, the linked table
Company3 and its appropriate columns are selected. (it is interesting to note the connection seems to work as it will show the column names when I click (+) to expand the table.) However, as soon I try to run the query, it fails with a ODBC Connection Error.

+++ Note +++ If I select a SQL Server ODBC driver instead of a Microsoft Access ODBC driver in the Microsoft Query Wizard, it will work fine. Data gets refreshed with no problem.

Here is what I have tried and observed:

1. Tested the OBDC connection through the ODBC Datasource Adminstrator. Works fine.

2. In Microsoft Query, selected SQL Server 2000 ODBC Driver.
Refreshed the Excel Spreadsheet. Works as mentioned above.
(The current configuration worked previously using an Access ODBC Driver to the linked SQL table.)

3. Created a new Access database with a link to Company3, thinking maybe the mdb file is corrupt. Still didn't work.

4. I created a new Excel spreadsheet and Microsoft Query to
to the linked table. Same results.

5. Compared the datatypes for Company3 against Company1 and Company2. Looked ok.

6. Copied Company3 into a test table. Tried Refreshing the Excel spreadsheet with Access ODBC Driver and the test table (all records). ODBC Connection failed. I even tried deleting all but one record in the table, but I obtained the same results.

7. Tried SQL Profiler to see if could give any useful information why the conncection failed. All I could find that it was testing the connection, but I could find any information why it failed.

9. Turned on ODBC tracing. Here is a snapshot of the log file:

msqry32 580-eb0 ENTER SQLDriverConnectW
HDBC 00892BD0
HWND 00000000
WCHAR * 0x74329A38 [ -3] "****** 0"
SWORD -3
WCHAR * 0x74329A38
SWORD 2
SWORD * 0x00000000
UWORD 3 <SQL_DRIVER_COMPLETE_REQUIRED>

msqry32 580-eb0 EXIT SQLDriverConnectW with return code -1 (SQL_ERROR)
HDBC 00892BD0
HWND 00000000
WCHAR * 0x74329A38 [ -3] "****** 0"
SWORD -3
WCHAR * 0x74329A38
SWORD 2
SWORD * 0x00000000
UWORD 3 <SQL_DRIVER_COMPLETE_REQUIRED>

DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (0)

DIAG [IM008] [Microsoft][ODBC SQL Server Driver]Dialog failed (0)

msqry32 580-eb0 ENTER SQLErrorW
HENV 00892B58
HDBC 00892BD0
HSTMT 00000000
WCHAR * 0x0012D488 (NYI)
SDWORD * 0x0012D4D4
WCHAR * 0x02417260
SWORD 4095
SWORD * 0x0012D4C0

msqry32 580-eb0 EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 00892B58
HDBC 00892BD0
HSTMT 00000000
WCHAR * 0x0012D488 (NYI)
SDWORD * 0x0012D4D4 (0)
WCHAR * 0x02417260 [ 66] "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed"
SWORD 4095
SWORD * 0x0012D4C0 (66)

msqry32 580-eb0 ENTER SQLErrorW
HENV 00892B58
HDBC 00892BD0
HSTMT 00000000
WCHAR * 0x0012D488 (NYI)
SDWORD * 0x0012D4D4
WCHAR * 0x024172F6
SWORD 4020
SWORD * 0x0012D4C0

msqry32 580-eb0 EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 00892B58
HDBC 00892BD0
HSTMT 00000000
WCHAR * 0x0012D488 (NYI)
SDWORD * 0x0012D4D4 (0)
WCHAR * 0x024172F6 [ 48] "[Microsoft][ODBC SQL Server Driver]Dialog failed"
SWORD 4020
SWORD * 0x0012D4C0 (48)

msqry32 580-eb0 ENTER SQLErrorW
HENV 00892B58
HDBC 00892BD0
HSTMT 00000000
WCHAR * 0x0012D488 (NYI)
SDWORD * 0x0012D4D4
WCHAR * 0x02417368
SWORD 3963
SWORD * 0x0012D4C0

msqry32 580-eb0 EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 00892B58
HDBC 00892BD0
HSTMT 00000000
WCHAR * 0x0012D488 (NYI)
SDWORD * 0x0012D4D4
WCHAR * 0x02417368
SWORD 3963
SWORD * 0x0012D4C0

msqry32 580-eb0 ENTER SQLFreeConnect
HDBC 00892BD0

msqry32 580-eb0 EXIT SQLFreeConnect with return code 0 (SQL_SUCCESS)
HDBC 00892BD0

msqry32 580-eb0 EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 00891B18

DIAG [S1000] [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'CompanyDatabase' failed. (-2001)

Not sure what is causing this error. I am leaning that it has to so some thing with the table (Company3) itself. Permissions? Any assistance on issue would be greatly appreciated.:-)

By the way, does anyone know why sometimes you get a login dialog when you open a datasource and sometimes not?

Hi! I have created Pivot tables in Excel 2000 that are using tables from a SQL Server 2k database. The client is running Win2k Professional with SP3 and Office 2000. The server is running Win2k Server with SP3, and SQL Server 2000 with SP2.

The SQL 2k installation has been setup for Mixed Security mode. To access data using the pivot tables, I created an ODBC connection in the System DSN area, with the necessary sa password for SQL authentication. The pivot tables work fine if I log on, but when I login with a test ID (created especially with proper rights to test installations) that has equal rights as myself, I notice the following things: -
1. The ODBC I created in System DSN when I logged in with my ID is nowhere to be found, so I have to create it once again as user Test.
2. The pivot table excel sheet does not refresh, it gives an ODBC error as follows: -
SQL State 28000
SQL Server error 18456
Login failed for user 'domain nameuser name'
3. A small dialog with the "Trusted Connection" checkbox ticked pops up asking for login ID and password. I untick the checkbox and enter 'sa' and the sa password - still the connecion does not happen and the data does not refresh in the pivot tables, and the error continues.
4. The problem gets sorted our ONLY if the pivot table is recreated when logged in as the Test user, or for that matter any other user who is required to use that pivot table, and the pivot tables work only on the machine and only for the user. Any other user logging on to this machine, and the pivot tables don't work!

My problem is, I cannot go around re-designing pivot tables on each client computer. The excel pivot tables have been created and saved on a shared location on the server, providing access to all the required users. I also cannot disclose the sa password to the individual users because that is a security hazard. I am just fed up trying to make this work, it refuses to budge. Your help will be really appreciated.

Your assistance will truly be appreciated.

Thanks.

Hello,

I was wondering if it was possible to have more than one SQL statement off one ODBC Connection?

And if the answer is 'Yes' how?

Many Thanks

Andy

Hi all

I have set up ODBC Connections to the databases which run behind the scenes of our inhouse software. I am trying to set up some reports in Access which extract this data and put it in a nice pretty format for everyone's veiwing pleasure, however I am having problems with some of the fields and how they appear on the report.

EG. Our phone number field (in the inhouse system) is set to be 40 characters (sometimes there may be more than one phone number entered in the field). In our inhouse program it is seen as:
"00 0000 0000"
however in the ODBC table it appears as:
"00 0000 0000____________________________"
where _ represents a blank space (ie in the ODBC table it recognises the rest of the field as spaces)

When i bring the field up in the report, it splits the phone number and wraps the text so that it looks as follows:
00 0000
0000

(NB. the Column width is sufficient to show the details of the phone number on the one line)

I tried adding an extra space at the end of the phone number (in the in house data entry field) however the program appears to automatically concatenate spaces at the end of fields.

I have tried changing the columth/height and have also tried changing the Can Grow/Can Shrink Settings however it still keeps splitting it this way. The only way that I have been able to get the phone number to show all on the one line is if the Can Grow/Can Shrink are both set to NO and the height of the field (in Design View) is the height of one line of text - which means that anything more than the first 20 characters or so is chopped off and doesn't appear on the report.

Would be grateful for any suggestions you have on how to combat this so that it only wraps on after say 16 characters or something.

Thanks

Megs

Hi

I have an Excel spreadsheet linked through to a sql table via and ODBC connection and I have a macro to refresh the data. I want the macro to first check that the ODBC connection exists before trying to refresh the data. So it would look like:

IF ODBC Connection exists THEN refresh data ELSE return warning message.

Any ideas....?

thanks

Hi

I have an Excel spreadsheet linked through to a sql table via and ODBC connection and I have a macro to refresh the data. I want the macro to first check that the ODBC connection exists before trying to refresh the data. So it would look like:

IF ODBC Connection exists THEN refresh data ELSE return warning message.

becoz when connection cannot be made, its gives me error. and i have to manually press ok button.

Any ideas....?

thanks

Hi guys,

The task was to create a link to dBaseIV DBF table, where the supplier info was stored, and based on entry in one cell (name of the supplier) in Excell, to return appropriate data to some other cells in the sheet.

I created an ODBC connection and parametrized query and it worked like a charm. But it was due to open DBF table in Excel.
As soon as I closed DBF table in Excel the returning result set through query was invalid.
I was getting results for some values that should not give results (always the same two records) and for names that I know exist in the DBF table the query didnt return any data.

What is wrong with this ODBC link I have no clue, please help.

Hello!

Is it possible to use MID fiunction in SQL Query?

Or is there any other way to get it done..
StrSQL = " SELECT " & _
" j.Accno, SUM(j.db)DR, SUM(j.cr)CR, Month(j.Trdate)Month,
LEFT(j.tktno,4)ARL"
I would like to get "MID(j.Accno, 5, 3)Loc"

three charecters from Accno starting fifth character.

Thanks

Hello

Assuming that there is already a workbook with the database connection and
the query set up with parameters, is it possible to add a User Form and use
the values eneterd by the user to construct a new database query.

Many thanks for any help

Moshe

Hello and thank you for your time, I scraped together some VBA code that imports several Access tables into Excel. The code works perfectly, it loads 12 worksheets with the contents of 12 tables. It does not matter if the MDB is open or closed, the code works either way (nice!). After I run the code, the MDB opens in read only mode. If I close the Excel workbook (which is host to the macro) then the MDB opens normally. Here's my question: How can I close the ODBC link (with VBA) without closing the Excel workbook? Below is an except of the code I scraped together with the help of the macro recorder (code between EZ3) and code that a co workhed provided (see code between EZ2).

Any help would be greatly appreciated. Below is an except of code used to establish ODBC connection and to load a worksheet.

'S010
Fetch data
FetchData:
Application.StatusBar = "Fetching data from " & GSN & " table..."

'Reset wrksht
EZ1 = 1: Sheets(GSN).Select: EZ1 = 0
Cells.Clear

'This block courtesy of Robert.....
EZ2 = 1
   Debug.Print PathAndFilename;
   strConnection = "OLEDB;" & _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Password=""""; User ID=Admin;" & _
        "Data Source=" & PathAndFilename & ";" & _
        "Mode=Share Deny Write;" & _
        "Extended Properties="""";" & _
        "Jet OLEDB:System database="""";" & _
        "Jet OLEDB:Registry Path="""";" & _
        "Jet OLEDB:Database Password="""";" & _
        "Jet OLEDB:Engine Type=5;" & _
        "Jet OLEDB:Database Locking Mode=0;" & _
        "Jet OLEDB:Global Partial Bulk Ops=2;" & _
        "Jet OLEDB:Global Bulk Transactions=1;" & _
        "Jet OLEDB:New Database Password="""";" & _
        "Jet OLEDB:Create System Database=False;" & _
        "Jet OLEDB:Encrypt Database=False;" & _
        "Jet OLEDB:Don't Copy Locale on Compact=False;" & _
        "Jet OLEDB:Compact Without Replica Repair=False;" & _
        "Jet OLEDB:SFP=False"
EZ2 = 0

'Refresh BackgroundQuery. This block courtesy of macro generator.....
EZ3 = 1
Debug.Print strConnection
    With ActiveSheet.QueryTables.Add(Connection:=strConnection, Destination:=Range("A1"))
        .CommandType = xlCmdTable
        .CommandText = Array(GSN)
        .Name = "787_AI"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = PathAndFilename
        .Refresh BackgroundQuery:=False
    End With
Sheets("MacroDashboard").Select: Cells(NAL, 3) = "Access table '" & GSN & "' has been
successfully imported.": NAL = NAL + 1
FetchDone:
EZ3 = 0


I need to pass date variables to a SQL query and return the data to Excel. I recorded a macro with fixed dates, and the data returns to Excel without any problems. However, when I replace the dates with variables, the data doesn't return to Excel. I checked the SQL statements, and it is correct. The titles of the columns only returns. I do not know why it doesn't return. Can somebody help me?

Here is the code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/24/2003 by T
'

'

Dim InitDate As Date
Dim FinDate As Date

InitDate = Range("A1").Value
FinDate = Range("A2").Value

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=CData;UID=DOC;PWD=r4v10nt;APP=Microsoft® Query;WSID=UL5;DATABASE" _
), Array("=TDB")), Destination:=Range("C7"))
.CommandText = Array( _
"SELECT History.RunID, History.Lineal, History.CreatedDate" & Chr(13) & "" & Chr(10) & "FROM TDB.dbo.History History" & Chr(13) & "" & Chr(10) & "WHERE " _
, _
"(History.CreatedDate>=" & InitDate & " And History.CreatedDate

I have several queries in my workbook that pull data from
a SQL database via an ODBC connection. We recently moved
the SQL database to a new server, so I updated the ODBC
connection. Now those queries will not run. I can create
new queries just fine using that connection. I manually
edited the .DQY files with the name of the new server
connections, and still they won't run. I get a SQL Server
error 4060: Connection Failed:Access to selected database
has been denied. Anyone have any solutions, besides
having to rebuild my queries? I'm on Win XP (SP 1), Excel
2000, Microsoft SQL Server 2000.

I have an excel sheet that I have MS Query attatched to. Recently, our servers were renamed to something different. I have changed my ODBC connections on my computer and updated my other excel sheets to point to the correct server via Script Editor, however, on one particular spreadsheet, the script editor is disabled and I can't update the path.trap-true, etc.

How can I tell this worksheet to point to the new server? There has to be something in the background where I can tell it to go.

BTW.....I've tried opening the Query and because the new server is down, it will not connect to allow me to cut and paste the SQL code to another worksheet.

Please help

I have a Access database connected to a SQL server. When I connect to the
Access database it requests the username and password to the SQL server.
Access is used to do queries that SQL Server cannot (we have a older
version). I added a link to Excel to create my charts (excel cannot handle
the full queries Access can). When I link Excel to Access to get my data,
any query or report that has a table that is linked to the SQL server I get
the following error: ODBC - connection to "[My SQL SERVER NAMEJ" failed.
Now, if I use a query for a table that I created that is not linked to the
SQL Server it links perfectly and I can refresh fine. Any ideas?
I connect by data - import external data - New database query Thanks!

Hello,

Wondering if is it possible to populate a combobox with values from a single column sql query.

1: I do not want to have the recordset returned to a query table and then have combobox populate from query table (trying to cut out the "middleman")
2: I have odbc connections only, I do not have the privledges to add on the ADO add-ons, and if I can get it, my clients will not have it.

so,
The connectionstring I use is this
Dim varconnection As String
        varconnection = "ODBC;DRIVER={SQL Server};Server=MyServer;DATABASE=MyDatabase;UID=that
dude;PWD=password"
Any help would be greatly appreciated

i have created an ADO SQL query and built 2 pivots from the resulting pivot cache. Problem is where i would like the user to tweek the query (lets say a different date range to use) and the new resultset to refresh the pivots without having to rebuild the pivot tables.

i have tried to achieve this by calling the original funtion in 2 different modes:
mode 1: to query and build tables from scratch
mode 2: to requery but only refresh the tables (no rebuild)

i have read about ptCache.EnableRefresh = True but this has not worked.

not sure if this is important but the database is Access 97 and i use excel 2003.

any pointers appreciated.

below i have included the main function with sql and pivot build code with mode switches and a helper refresh function.


	VB:
	
 
Public Sub Create_PivotTable_ODBC_MO(strMode As String) 
     
    Dim stCon As String 
    Dim stSQL As String 
    Dim rngTemp As Range 
     
    stCon = "ODBC;DSN=MS Access Database;DBQ=XXXXX.mdb;uid=Admin;pwd=;MaxBufferSize=2048;PageTimeout=5" 
     
    stSQL = "SELECT * FROM YTDData where TradeDate > " & "#" & CDate(Range("startDate").Value2) & "#" & " AND TradeDate

Hello... This is my first post... so I hope this makes sense.

I am pulling data from our database and I am able to get the information that I want. The problem is that when I created a UserForm to change the parameters of the query, I get the error on the ".refresh Backgroundquery:=false" line (General ODBC Error). I made a macro already, so when I push the button, I just have to log onto the server and the query works. On the Form, I created 3 Fields: Fund, Date, YearMonth. I want to be able to run the query with the data in the fields. It's when I update the fields is where the problem occurs.

Here's what I have:

	VB:
	
 FundNumberButton_Click() 
     
    Dim FNText 
    FNText = InitialForm.FNTextBox.Value 
     
     '
    With Selection.QueryTable 
        .Connection = Array(Array( _ 
        "ODBC;DSN=QDB Production - SQL Server;UID=efjai;;APP=Microsoft Office
2003;WSID=RA-196;DATABASE=qdb;Network=DBMSSOCN;Address=obiwan.qd" _ 
        ), Array("b.ucla.edu")) 
        .CommandText = Array( _ 
        "SELECT gl_trans_closed.location_code, gl_trans_closed.account_number, gl_trans_closed.cost_center_code,
gl_trans_closed.fund_number, gl_trans_closed.project, gl_trans_closed.sub_code, gl_trans_closed." _ 
        , _ 
        "object_code, gl_trans_closed.source_code, gl_trans_closed.trans_id_gl, gl_trans_closed.trans_doc_date_gl,
gl_trans_closed.trans_amount_gl, gl_trans_closed.trans_desc_gl, gl_trans_closed.trans_ref_gl, " _ 
        , _ 
        "gl_trans_closed.ledger_year_month" & Chr(13) & "" & Chr(10) & "FROM qdb.dbo.gl_trans_closed gl_trans_closed" &
Chr(13) & "" & Chr(10) & "WHERE 
         
         'Here's where I would like the text fields off the User form to be updated:
        (gl_trans_closed.fund_number=[B]FNText[/B]) And (gl_trans_closed.trans_doc_date_gl>=[B] '011509'[/B]) AND
(gl_trans_closed.ledg" _
        , "er_year_month>=[B]'200901'[/B])") 
        .Refresh BackgroundQuery:=False 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I don't use the variable, FNText, everything is okay... but after I try to use fields from the form, I get the error.

Thanks in advance.

-Jovan

arg... yeah man im pretty much in the same boat ... all i need to do is
change the dsn system name

did you have any luck?

"makrug" wrote:

> I have a spreadsheet which contains 30+ pivot tables which have an external
> mdb as their data source. If I change the location of the mdb then I repath
> the ODBC link to the new location and manually edit the SQL code in MS Query
> to change the path for each pivot table. Rather tedious. I've trawled this
> site for some code to do this, but no luck so far.
>
> A second related question. When building external data queries using MS
> Query I use a pre-defined ODBC link and select that as the DSN. Now I would
> have thought that if the external data source moved all I would need to do is
> repath ODBC connection and all will be peachy fine, but this appears not to
> be the case. The old path is being remembered somewhere.
>
> Any help appreciated.


No luck finding an answer? You could always try Google.