Error 3146: ODBC Call Failed

When you first create a link to an ODBC data source, Access will memorize the underlying table design, but it will not automatically update itself if the table design in changed; resulting in Error 3146 ODBC Call Failed.

This is in sharp contrast to the behavior when linking to external native Access tables - Access will automatically update the table definition, so no action is required when the design of the linked table is changed.

To update links to ODBC data sources, in Access 2002 or newer you can right-click on any linked table and then click on Linked Table Manager. This displays a list of all linked tables and their locations. Simply check-mark the table(s) to be refreshed and click OK. Access will then update the memorized table designs. (In older Access versions, the Linked Table Manager is found on the Tools menu.)

One caveat - you cannot use this method if you are trying to relink to an ODBC data source that uses a different driver. For example, if you have a DSN that points to a SQL Server 2000 database using the SQL Server provider, and you change the DSN to point to a SQL Server 2005 database using the SQL Native Client; this method will not work - Access will not recognize the new connection. In that case, you will need to actually delete the linked tables and then re-link using your new DSN.

Scripting T-SQL

Sql Server displays the creation date for db objects, but does not show the last modified date. That makes it hard to tell if and when an object like a view, sp, or UDF was last changed.

For that reason, I always write my script and save it to a disk file, using the following general structure:

DROP PROC dbo.MyProcedure
GO

CREATE PROC dbo.MyProcedure
AS
-- actual code goes here
GO
GRANT EXECUTE ON dbo.MyProcedure TO [MyUserName]
GO

This has two benefits:
1. I can run the script on the dev system to verify it works, and make any needed changes
2. When I run the final script on the production system, the creation date will actually represent the date last modified

IMPORTANT: You cannot use this method for tables, since you will lose all the data therein!

Create SQL Server database query with MS Excel

1. Start a new XL workbook
2. Click Data ~ Import External Data ~ New Database Query
3. At the "Choose Data Source" dialog, either select an existing DSN or create a new one, then click OK.
4. At the "SQL Server Login" dialog, enter your credentials and click OK
5. At the "Query Wizard" dialog, click Cancel
6. At the "Do you want to continue editing this query in Microsoft Query?" click Yes.
7. At the "Add Tables" dialog, click Close
8. On the MS Query toolbar, click the SQL button
9. Enter your SQL statement in the "SQL" box and click OK
10. If your SQL statement is a stored procedure, it will say "SQL Query can't be represented graphically. Continue anyway?" - click OK
11. You should now see your data displayed in MS Query
12. Click the Return Data button (looks like an exit door) and MS Query will close
13. Now you're back in XL. At the "Import Data" dialog, select the location for the top left corner of where you want the data. It defaults to cell A1 which is usually where you do want it to start; click OK
14. Excel will now retrieve the data and place it starting from the chosen location

Using BETWEEN with dates

T-SQL has a handy BETWEEN statement that makes it easy to apply date range criteria. The basic usage is

WHERE dtSomeDate BETWEEN '1/1/2007' and '1/31/2007'

...which can replace the cumbersome

WHERE dtSomeDate >= '1/1/2007' AND dtSomeDate <= '1/31/2007'

either expression would (in theory) retrieve any date during January 2007. But SQL Server dates can contain a time component - so if dtSomeDate was actually '1/31/2007 08:00:00' meaning 8:00 AM on the 31st, that date actually would fall outside the desired range.

I picked up a tip from SqlServerCentral that removes the time - you could write
DATEADD(d,DATEDIFF(d,0,dtSomeDate),0)
and I even re-posted that tip at my web site, but every time I need to use it, I have to go look it up. So I wrote the following function that is easy to remember & can be invoked whenever comparing dates:


CREATE FUNCTION dbo.fnNoTime
(@DateTime datetime)
RETURNS datetime
AS
BEGIN
DECLARE @date smalldatetime
SET @date = DATEADD(d,DATEDIFF(d,0,@DateTime),0)
RETURN @date
END

...returning to my original example, you would use the function as follows:

WHERE dbo.fnNoTime(dtSomeDate) BETWEEN '1/1/2007' and '1/31/2007'

Works with Vista

There are a surprising numbers of free utilities that work with Windows Vista.

My faves are FoxIt Reader, a fast & free replacement for Adobe Reader; NotePad2, a smart NotePad replacement for programmers that color-codes text based on file extension; and Ad-Aware SE Personal, which scans for spyware & other such nasties.

Thus far, every PDF that I've opened with FoxIt has displayed just fine, without the long loading times (and frequent bloated updates) of Acrobat. The only downside is that sometimes IE doesn't recognize that FoxIt can open a PDF, and presents the dumb "Windows cannot open..." message.

NotePad2 is a must for programmers (like me) who sometimes still print out listings, especially for long or complex programs. It uses the file extension to apply the appropriate color scheme for that file type. The source code is available for the curious (or compulsive tweaker) and I've even had the program running under Linux using the wine Windows emulator.

Next is the excellent Ad-Aware which protects from spyware, much more effectively than the native Windows Defender - even after this morning's Defender definition update, Ad-Aware found five tracking cookies & allowed for their easy quarantine & subsequent removal.

My final fave is a VB6 program that I wrote to print the screen, and what do you know? It also works with Vista. Imagine that.

Blogger Support is Super

If you've noticed that this blog was MIA for the last few days, I had been fooling around with a 3rd blog, then decided to discard it -but- I accidentally deleted THIS one. Oops. And I had no backups, shame on me.

Blogger Support to the rescue - "Karl" was able to restore all my posts going back to Jan 2005. All the more impressive, considering that this is a free service provided by Google.

Thanks to Karl and all of the Blogger Support team.

Welcome

Welcome to the Mitchell Data Base blog. On these pages, I hope to cover topics of interest to database consultants and users. The primary database platforms include: Microsoft Access and Microsoft SQL Server, but older platforms such as dBase may also be covered from time to time. Any and all comments are welcome. If you have sample files or other materials to contribute, please send an email to webmail@wvmitchell.com and you will be contacted privately with posting information. Thank you for your interest.