Query query - very strange

[Gargamil]

After many years of using Access I've struck a curly one that makes no sense
at all. I'm trying to create an append query to convert data from one
database into a new database with a different table structure. Now the
query itself is simple but the strange thing is the way the destination
table field names are being treated by the software.

There are about 50 fields in the table of which about 30 are of the general
format xxxxxx (ppm). Note the space between xxxxxx and (ppm). When I
create the query and select the destination field and run the append,
everything works fine and the data moves across to the new table. The
strange thing is if I save the query and try to run it again every
destination field name is now xxxxxx(ppm) - missing the space. This of
course means the query falls over until I edit all the field names again by
inserting the space.

Why, why, why would access decide to change the field names on me every time
I save the query??? I cannot rename the destination fields so what's the
solution.

g

 

[Allen Browne]

Garamil, if I remember correctly, this problem is with the query design
interface. If you execute the query statement in code, it will work
correctly.

Create the query the way you want it, and switch to SQL View (View menu.)
Check that the spaces are in the field names, correcting any that need to be
changed. Then copy the SQL statement to clipboard.

Create a new form: design view, no table.
Add a command button from the Toolbox (View menu).
Name the button (say) cmdMakeTable.
Set it On Click property to [Event Procedure].
Click the Build button beside that property. Access opens the code window.

Set up the event procedure so it looks like this:
Private Sub cmdMakeTable_Click()
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub
Paste the SQL statement into the 3rd line.

IIRC, this will create the table correctly.

 

[Gargamil]

Hello, Allen!

All worked fine thanks. Any ideas why Access vagues like that??

Rod

You wrote on Sat, 26 Nov 2005 16:58:45 +0800:

AB> Create the query the way you want it, and switch to SQL View (View
AB> menu.) Check that the spaces are in the field names, correcting any
AB> that need to be changed. Then copy the SQL statement to clipboard.

AB> Create a new form: design view, no table.
AB> Add a command button from the Toolbox (View menu).
AB> Name the button (say) cmdMakeTable.
AB> Set it On Click property to [Event Procedure].
AB> Click the Build button beside that property. Access opens the code
AB> window.

AB> Set up the event procedure so it looks like this:
AB> Private Sub cmdMakeTable_Click()
AB> Dim strSql As String
AB> strSql = "INSERT INTO ...
AB> dbEngine(0)(0).Execute strSql, dbFailOnError
AB> End Sub
AB> Paste the SQL statement into the 3rd line.

AB> IIRC, this will create the table correctly.

AB> --
AB> Allen Browne - Microsoft MVP. Perth, Western Australia.
AB>
AB> Reply to group, rather than allenbrowne at mvps dot org.

AB> ??>>
??>> After many years of using Access I've struck a curly one that makes no
??>> sense at all. I'm trying to create an append query to convert data
??>> from one database into a new database with a different table
??>> structure. Now the query itself is simple but the strange thing is
??>> the way the destination table field names are being treated by the
??>> software.
??>>
??>> There are about 50 fields in the table of which about 30 are of the
??>> general format xxxxxx (ppm). Note the space between xxxxxx and (ppm).
??>> When I create the query and select the destination field and run the
??>> append, everything works fine and the data moves across to the new
??>> table. The strange thing is if I save the query and try to run it
??>> again every destination field name is now xxxxxx(ppm) - missing the
??>> space. This of course means the query falls over until I edit all the
??>> field names again by inserting the space.
??>>
??>> Why, why, why would access decide to change the field names on me
??>> every time I save the query??? I cannot rename the destination fields
??>> so what's the solution.


With best regards, Gargamil. E-mail: (e-mail address removed)

 

[Allen Browne]

That particular one is a bug in how the Query Designer works.

The query designer is actually a decent piece of software, but it does have
its quirks, bugs, and limitations. This one is a bug.