ACCESS FEED: RE: [MS_AccessPros] Queries Work Then They Don't

Further follow-up. I removed the UNION from my query to a simple list of all rows, and it worked fine in Access … but in Excel the query still returned no rows.

This is silly!

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 30 November 2014 12:43
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Queries Work Then They Don't

Following up on this, I have since found that remoiving ORDER BY works with a single select, as soon as I UNION it fails again. So this query

SELECT NullLong AS RegionID, "[All Regions]" AS Region
From ztblNullID

UNION SELECT reg.RegionID, reg.Region
FROM Region AS reg;

Doesn't show the regions, just the record from ztblNullID.

What is going on?

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 30 November 2014 12:33
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Queries Work Then They Don't

John,

That statement simply loads a named range in a given Excel workbook into a targeted Access table. The actual code is in its own procedure and the three variables, workkook, range name and target table are all passed as parameters and I messed up somewhat in trying to show it as it would resolve, the actual statement should read

INSERT INTO [Region] IN ''C:\Users\Bob\MyProject\MyDB.mdf SELECT * FROM [LoadData]

I think we are focussing on the wrong thing here. As I have said, I am using the Access database purely as a repository for the data, the users don't use Access at all, their application is all within Excel, the data is loaded from Excel, the queries are returned to Excel. The tables load correctly, all of them, and from within my Access I can see all of the data in those tables and it all looks fine. However, if I run one of the queries, an example of which I showed earlier, it returns no data. When I run my query from Excel, it too returns no data.

Some of the tables load fine every time, Property, Status, etc. But Region and Country are loading and querying okay the first time after I rebuild the table, but subsequent re-runs they seem to load okay but the query returns nothing. I cannot see any discernible differences between say the Region table and the Status table, both have a numeric id, and a text name field. And I cannot see how a table that has data can return nothing the query, even a simple select named fields query.

I have just done some further testing:

- SELECT * FROM … works fine

- SELECT RegionID, Region FROM … works fine

- SELECT reg.RegionID, reg.Region FROM Region AS reg ORDER BY reg.Region doesn't work

- SELECT reg.RegionID, reg.Region FROM Region AS reg ORDER BY 2 doesn't work

So it seems that any query including an Order clause gives me problems. As the same code is working fine the first time after rebuilding the table, it suggests to me some form of corruption.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 29 November 2014 21:26
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Queries Work Then They Don't

Bob-

I don't understand what this statement is doing:

mpSQL = "INSERT INTO [Region " & _

"IN ''C:\Users\Bob\MyProject\MyDB.mdf' " & _

"SELECT * FROM LoadData[]"

If I'm reading that correctly, mpSQL should contain:

INSERT INTO [Region IN ''C:\Users\Bob\MyProject\MyDB.mdf SELECT * FROM LoadData[]

There appear to be unbalanced brackets - two "[" and one "]".

Where or what is "Region"?

I would think it would be better to link to the Excel data and then load it into an Access table.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Nov 29, 2014, at 8:00 PM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

No linked tables, just simple loads from Excel using ADO. I connect like so

Set mpConn = CreateObject("ADODB.Connection")

With mpConn

'Connect to the Excel source File

.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source='C:\Users\Bob\MyProject\LoadAttributes.xls "';" & _

"Extended Properties='Excel 8.0;Header=Yes;IMEX=1'"

.Open

And then load into the table like so

mpSQL = "INSERT INTO [Region " & _

"IN ''C:\Users\Bob\MyProject\MyDB.mdf' " & _

"SELECT * FROM LoadData[]"

.Execute mpSQL

.Close

End With

LoadData being a named range within the Excel workbook.

As I said earlier, I think I am corrupting my table or the setup in Access somehow when I reload, but I don't understand how, nor why it just seems to affect a few tables, others load perfectly every time.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 29 November 2014 15:40
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Queries Work Then They Don't

Bob-

Are you saying that Region is a linked table from Excel? If not, I'm not seeing what you are doing.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Nov 29, 2014, at 4:20 PM, 'Bob Phillips' bob.phillips@dsl.pipex.com <mailto:bob.phillips@dsl.pipex.com> [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> > wrote:

John,

A couple of points I didn't clarify earlier is that I cannot query the table using a simple SELECT fields query on these tables (although if I double-click Region in the Tables pane I see all of the data), and I am not using the Access UI, I just use the database to load and query from Excel.

I like the idea of the null values table, but it seems to me that the problem is not in the query but somehow the table has been compromised by the reload. Applying your suggestion made no difference, it still listed nothing.

Bob

From: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 29 November 2014 13:41
To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Queries Work Then They Don't

Bob-

It's always problematic trying to create an "All" value for the Row Source of a combo box. My technique is to create a dummy table with an AutoNumber Primary Key and one each of the data types that I might want to use as the "key" value for my dummy row. I call this table ztblNullID with a Long Integer (NullLong), Integer (NullInt), and Text (NullText) fields, and I make sure that the Text field has Allow Zero Length set to No. I then create one row by typing a space in the Text field and saving the row. This results in a Null value in the two number fields and the text field. Then in your situation I would do:

SELECT NullLong, "[All Regions]" As Region

FROM ztblNullID

UNION

SELECT reg.RegionID, reg.Region

FROM Region

ORDER BY Region;

Note that the "All Regions" value will never show in the combo box because the bound value is a Null, but the user can select that row to set the combo to Null to indicate you want all.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Nov 29, 2014, at 11:01 AM, 'Bob Phillips' bob.phillips@dsl.pipex.com <mailto:bob.phillips@dsl.pipex.com> [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> > wrote:

I have an application where I reload a number of property related tables from a list maintained on a spreadsheet by the users. The list contains all of the details for the property, id, name, region, country, city, etc. I take all of the attributes into separate lists, de-dup them, add a numeric id for each, delete the existing items in that attributes tab le (e.g. region), and load each of the newly derived id/name pairs into their own table.

This works fine, all of the tables are loaded and look fine.

I also have some simple queries to produce lists of all of these attributes that I can use in an excel app. A typical SQL for one of these list is as follows

SELECT TOP 1 0 AS RegionID, "[All Regions]" AS Region

From Region

UNION ALL SELECT reg.RegionID, reg.Region

FROM Region AS reg

ORDER BY 2;

The first time I run this query, it works fine. If I then reload the property table, something odd happens. All of the tables look fine, but some of those list queries return nothing – the table has all of the details I would expect, but the query doesn't work.

If I delete the table, and reload them all it works, but the next reload we are back to missing lists.

Region seems to be the worst culprit, but Country and City and showing similar behaviour. Each of these tables are simple numeric ID and text name columns, primary key on the ID. I don't have the relationships setup in the database yet.

Anyone have any idea what is going on, and how I can prevent it?

[Non-text portions of this message have been removed]

------------------------------------
Posted by: "Bob Phillips" <bob.phillips@dsl.pipex.com>
------------------------------------

------------------------------------

Yahoo Groups Links