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.

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 :

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.

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.

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!

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.

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.

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.

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:

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:

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.

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.

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.

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?

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.

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!

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.

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.

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.

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.