1467 records went in
1453 came out
repeatedly
consistently.
I had great fun recently tracking this down.
The system creates an Access database on the fly, adds a bunch of tables and queries, populates it all then pulls the final answer of the stack of queries back into Excel. I could have done it in one SQL statement, but it would have been the size of a book.
Anyway when running it from Access everything was fine, when triggered from Excel someone nicked (a few of) my records!
Back to Access, everything fine, Excel wrong, Access still ok…
Eventually, after much digging, I remembered a previous painful ADO encounter. One of my queries was using the ‘*’ wild card which is fine in Access, but of course in ADO it is ‘%’!
One of my colleague then suggested a neater solution than the brute force approach I was about to commit:
SELECT blah blah from blah where (x Like “*fut*”) OR (x like “%fut%”)
This will work with either driver without the need for separate queries (the route I was considering).
Have you been burnt by this different wild card issue?
cheers
Simon
Smurf on Spreadsheets 
Friday, 25th March, 2011 at 11:00 am |
I’m sure you already know about this Simon, but I just wanted to point out to your readers that SSIS is also a tool that is worth getting to know. It is Microsoft’s attempt to eat up the ETL market held by Cognos, Oracle, etc.
There is a bit of a learning curve, but this is a very powerful tool and tends to be more robust than Access.
Friday, 25th March, 2011 at 11:06 am |
As a certain insurance pimping dog might say, “Ohhhhhh yes!”
My SQL bothering subs have a boolean wildcard substitution parameter which will trigger the swap if needed.
Friday, 25th March, 2011 at 3:40 pm |
Totally been burned by this, and it’s a real headscratcher when you’re trying to implement ADO for the very first time. I haven’t had to create any new solutions in a year or so, so would probably have bashed my head against it next time.
This looks like a great solution. Now the trick will be trying to remember you posted it!