Hello

I'm new with Access and I have this problem.

I created a database with a table that load his data from a txt file
and all works good

I have 76 records and it is well fetched in my database and when I
display it all is good.

Now in my macro... I added a SQL code that delete all the records in
the table before loading the new data.
There is 76 records but it tell me that it want to delete 152 records.
But I displayed the table data before and a saw only my 76 records.

Then I created a VB sub that count my records in my table and it said
152 !!
This his the double than what I see in my table and in my txt file !?

So please can you tell me what is the problem ?

Thank you very much
Jiador

I suspect the table's index was corrupted when running the SQL code from the
macro.

I would run Compact and Repair Database from (menu bar) Tools to do an
automated repair.

Rather than running simple SQL code from a macro, I'd suggest building a
small
Delete Query to delete the records in the Query Design window. Save it as a
query
and have the Macro run the query.

The reason: I've seen cases where perfectly standard SQL code would cause
problems
in Access if the Access query design window was not used. This was when the
straight SQL code was fed directly to the Access database, not via the query
window.

By designing the query in the query design window,
you let Access "tweak" the SQL statements to be optimized for Access.
I know that this cure should not be needed and would be expected not to
help, but it did work in my case (when linking to Access file via an asp.net
web page).

Bruce Pick

"Jiador" <laverf (AT) hotmail (DOT) com> wrote

I've never had trouble with valid SQL code written "outside" of the the QBE
.... what problems have you encountered?

"Bruce Pick" <brucepick1 (AT) comcast (DOT) net> wrote

Scott,
I was building asp.net web pages for a course I took one year ago. The
problems were with SQL Insert statements. They were fairly simple
statements, just to insert one record with a few fields, with all fields
populated appropriately.

I verified that the standard SQL output from asp.net was correct by
displaying it on screen, copy and paste into Access as an SQL statement. It
ran fine from there. When run from the asp.net page I would get an error
message.

In any case, the code worked after doing this:
I viewed my basic code in the Access query designer,
letting the query designer modify the SQL statement
as Access prefers it to be formatted.
Opened the Access SQL window to view the query,
Copied the format of the query with all the [ ... ] and (... )
and quote marks and capitalization exactly as built by Access.
Then built my asp.net web page to send code to Acccess in that
exact format. Then the insert statement would run, every time!

You are probably thinking that there must have been an error in my original
SQL code on the asp.net page. Not true. I coped the resulting SQL from the
web page and pasted directly into Access, and it ran perfectly. Just
wouldn't run from the asp.net page. I also should say that I've had plenty
experience building SQL code on Access VBA forms, where you assemble the SQL
as a concatenated string with lots of single quotes, double quotes and
variables of different datatypes - you get the idea. I was already pretty
much of an old hand at that when I took the asp.net course.

Bruce Pick

"Bruce Pick" <brucepick1 (AT) comcast (DOT) net> wrote

Bruce, Thank you very much for you answer.

I understand want you told me to do but,
don't laught at me , I don't know how to run a Access query in a
macro.
I don't see a macro Action who do that !
I find out a lot of thing with macro but this I'm think than monday
morning is not a good time for that ! :-)


Jiador

All right !!
All work great now.

When i tried your solution, I did not see or understand properly the
property of the action a had to take for loading my query.

Now all is good
Thank you very much :-)

Jiador

Jiador,

I'm glad you were able to make it work. It's good to get your response
- often I write, and never learn if my help was good.

My best,
Bruce Pick