Import data "changes" Ansi-SQL query mode

[Luke M.]

I'm having a problem with my database, which is set to use "SQL-Server
Compatible Syntax (Ansi-92)". My Ansi-92 queries work fine, until I do a data
import: either through "Get External Data" from the file menu, or if I choose
to do the import programmatically with VBA. After the import completes, any
queries I have which use 92 syntax, such as the '%' wildcard, no longer work.
If I change the wildcards to the Ansi-89 syntax of '*', they then work.

After the import, if I look in Tools-Options-Tables/Queries, my Ansi-SQL
query mode is still set to Ansi-92: so it doesn't change that setting.
However, the effect on the database is just as if it had.

I can have the queries work again if I close the database after the import,
and open it up again. At that point, all my queries which use Ansi-92
specific syntax work again, as they should, and if I were to use any 89
syntax, it doesn't work, as they shouldn't. That is, until the next data
import, at which time it all becomes switched around again.

This is a huge pain for me. Anyone else had the same problem, or know how to
correct it?

 

[Brendan Reynolds]

What if you execute ...

SetOption "ANSI Query Mode", True

.... after the import?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Luke M.]

No, sorry, I tried that. It has no effect.

It's as if 'officially' the database is still set to Ansi-92: even without
running a SetOption, I can do a GetOption right after the import and it will
return Ansi-92 (returns True). However, the effective reality is as if the
setting had been changed.

 

[Brendan Reynolds]

I'm guessing that the import loads an MDA, and that the MDA's setting is
over-riding your MDB's setting. Possibly some experimentation with using
DbEngine(0)(0) or CodeDb instead of CurrentDb might help. Or you could try
introducing some delay after the import. Have you tried calling DoEvents
after the import?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Luke M.]

I did try DoEvents, to no effect.

I don't know much about the format Access gives loaded items or data. But in
my case, what I'm trying to import is not another Access object, but an Excel
file or Text file or something of the sort. The data actually comes in a
dBase file, and the users can convert it to Text or a spreadsheet, but my
code is for the process of automating the actual transfer of that data into
the mdb. So perhaps Access does convert the file into an MDA on import, I
guess I don't know enough to say, but it seems like that would only happen
when importing data from another Access database, not when from a different
file format like .txt or .xls.

I should say, if the data did come in an Access table, I could transfer it
to the current database using a SQL Select Into statement. We use this for
some other things and it works fine, no change in the SQL Query Mode there.

This code in VB causes the change in the query mode:

DoCmd.TransferText acImportDelim, "MySpecificationFile", "MyImportTable",
MyFilePath, True

Or,

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"MyImportTable", MyFilePath, True, "MyImportRange"

In fact, I suspect all the variations of the different DoCmd.Transfers will
cause what I observe. I've tried most all of them and had the same problem.

In these cases, I don't see how the CurrentDb could really come into play,
but perhaps I'm missing something.

Is there some other way besides the Transfer methods to import data? I don't
know of any, but that might solve my problem if there were.

 

[Brendan Reynolds]

I didn't mean to suggest that Access was converting the data to an MDA, but
that calling the TransferText method might cause Access to load an MDA into
memory, in the same way that, for example, using a form control wizard
causes acwzmain to be loaded. But now that I've had an opportunity to
investigate that idea a little, I can't actually see any evidence that it
happens, so it appears I may have been on the wrong track there. I'm afraid
I don't have any other suggestions at this time.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I did try DoEvents, to no effect.

I don't know much about the format Access gives loaded items or data. But
in
my case, what I'm trying to import is not another Access object, but an
Excel
file or Text file or something of the sort. The data actually comes in a
dBase file, and the users can convert it to Text or a spreadsheet, but my
code is for the process of automating the actual transfer of that data
into
the mdb. So perhaps Access does convert the file into an MDA on import, I
guess I don't know enough to say, but it seems like that would only happen
when importing data from another Access database, not when from a
different
file format like .txt or .xls.

I should say, if the data did come in an Access table, I could transfer it
to the current database using a SQL Select Into statement. We use this for
some other things and it works fine, no change in the SQL Query Mode
there.

This code in VB causes the change in the query mode:

DoCmd.TransferText acImportDelim, "MySpecificationFile", "MyImportTable",
MyFilePath, True

Or,

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"MyImportTable", MyFilePath, True, "MyImportRange"

In fact, I suspect all the variations of the different DoCmd.Transfers
will
cause what I observe. I've tried most all of them and had the same
problem.

In these cases, I don't see how the CurrentDb could really come into play,
but perhaps I'm missing something.

Is there some other way besides the Transfer methods to import data? I
don't
know of any, but that might solve my problem if there were.

 

[Luke M.]

Yes, now that you explain it that way I can see what you mean. I knew about
the acwzmain but that's about all, I guess there are probably all kinds of
things Access loads into memory at various times.

I appreciate your help in any case, Brendan. I guess I can always go through
all my queries and make sure they all conform to Ansi-98. I think I could
still accomplish everything with the old standard, but I shudder to think of
how many hundreds of lines of code I'll have to check...

If it seems I can replicate this problem in a simple DB, is it something I
should report to MS as a possible bug?

 

[Brendan Reynolds]

Have you checked the KB? Here's a link to the result of a search using the
keywords "Access" and "ANSI 92". There's quite a few of them. At first
sight, none of them seem to describe exactly the problem you're
experiencing, but I've only scanned the result page, I haven't read any of
the articles in detail, so it's possible I might be missing something ...

http://search.microsoft.com/search/...3&st=b&qu=Access+"ANSI+92"&swc=3&na=31&cm=516

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Yes, now that you explain it that way I can see what you mean. I knew
about
the acwzmain but that's about all, I guess there are probably all kinds of
things Access loads into memory at various times.

I appreciate your help in any case, Brendan. I guess I can always go
through
all my queries and make sure they all conform to Ansi-98. I think I could
still accomplish everything with the old standard, but I shudder to think
of
how many hundreds of lines of code I'll have to check...

If it seems I can replicate this problem in a simple DB, is it something I
should report to MS as a possible bug?

 

[Luke M.]

I have searched the KB, for hours it seems, but thanks for the link in any
case. There are some articles tangentially and only slightly related . They
dealt with how queries that define an export dataset must be written in the
syntax that accords with the version of Jet your ODBC connection will use
when exporting said data. For instance, if I were to export a dataset created
from a SQL query that used Ansi 89 syntax, and my ODBC connection to the
external source used Jet 4.0, then the resulting export would consist of zero
records, even if that same query returned records within Access.

There's nothing mentioned about imports, and in any case, this says nothing
about the database's Query mode unexpectedly changing in midstream.

One article did mention that changing the Ansi Query mode programmatically
has no effect until the program is closed and opened again, by design. So
that must be why using SetOption doesn't work. If you change the Query mode
through the tools menu, Access closes, compacts, and then reopens the
database for you automatically, which is how it can change the mode that way.

It appears that in my case I've found an exception to the rule that the mode
can only change on a re-open. Perhaps a process similar in principle to
recreating the database occurs when imports are executed. Even if so, I would
think the default Query mode it might change to would be 92, given the
version of Jet that Access is using. But apparently not.

 

[Luke M.]

I don't know if this helps or not, but I've done some further testing and
found some interesting behavior.

What I am actually trying to do on the several forms that break after the
import, is show some information in various listboxes. The user has the
option of typing in some information, like the first few letters of a name,
and VBA constructs a SQL statement which is then inserted into the RowSource
property of the listbox. The listbox is then Requeired, and voila, they now
have a filtered list in the box. Simple stuff.

These listboxes are the things which no longer work after the import, unless
I modify the SQL statements I put into their RowSource to use Ansi 89
wildcards.

However, I discovered that if I ran those same SQL statements in the query
window, just by creating a new saved Query and inserting the SQL statement,
they work just fine both before and AFTER the data import, using 92
wildcards.

So, I figured that instead of putting the SQL statement directly into the
RowSource of the listbox, what I would do is set the RowSource equal to the
saved query. When the user wants to filter the box, the VBA now updates the
query definition of the saved query, and then requeries the listbox, but the
listbox RowSource stays the same.

I test this out. After an import, the listboxes still break. This is quite
strange, because the saved query which their RowSource is set to actually
does work, if I go run it in the database window. It returns records like it
should. The listbox simply won't show them.

Even stranger, if I now (after the import) change the syntax to ANSI-89, the
saved queries return nothing when I run them in the database window, as they
shouldn't, since my database is set to 92. BUT, when I set the listbox
RowSource to that same 89 query which returns nothing in the database window,
the listbox shows all the appropriate records as if it works just fine.

What an import appears to do is change the query mode of a ListBox from ANSI
92 to 89 (perhaps of other controls as well, haven't tested). It doesn't
matter if the listbox RowSource is set directly to a SQL statement or to a
saved query.

All other ANSI 92 queries in the database work as they should, both before
and after an import.

Does this give any possible indication as to what might be going wrong, or
what I should research next to find some resolution to this?