Who nicked my data?

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

Advertisements

3 Responses to “Who nicked my data?”

  1. Keith A. Lewis Says:

    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.

  2. Gordon Says:

    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.

  3. Ken Puls Says:

    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!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: