Mitchell Data Base

SQL - list columns in a table, updated

2012-01-12T20:56:00.008-07:00

Way back in April 2009, I had published the original version of my stored procedure wm_columns which can be used instead of sp_help or sp_columns to view a list of column names for a table or view.The new version has a number of enhancements, and you can view the complete code here.The first parameter is required, which is the name of a table or view, such as:wm_columns 'my_table'...and the

SQL Timestamp and Access

2011-06-30T05:26:00.014-07:00

The timestamp field in a SQL Server table holds a unique value that is updated every time a record is updated. In a recent post in a SqlServerCentral forum, there was a suggestion that when you are using MS-Access as the front-end, you should add a timestamp column to every linked table - and thus prevent write-conflict messages.If you use the SQL Server Migration Assistant (SSMA) to convert your

T-SQL Computed Columns, part 3

2011-05-15T07:59:00.019-07:00

My last two posts described how to create a computed column, and how to control the data type and size.In the first example, the computed column would be "A" if there is an approval date, otherwise it would be "".The general rule is that the computed column can only refer to other columns within the same table. However, it is possible to use a scalar function to perform a lookup from another

T-SQL Computed Columns, part 2

2011-05-07T07:49:00.012-07:00

My previous post presented the basics of using computed columns in SQL Server. Here we have a slightly more complex example that also deals with data types and sizes.I have a table of locations in the city - each location has a direction, street, and cross street, such asNB Austin Blvd @ Cermak RdSB Austin Blvd @ Cermak RdEB Cermak Rd @ Austin BlvdWB Cermak Rd @ Austin BlvdIf you examine the list

T-SQL Computed Columns

2011-04-23T06:53:00.008-07:00

A Computed Column in SQL Server allows you to create a read-only calculated field that performs some logic but occupies no space in the database.We have an Access front-end with a SQL Server database & I was looking for ways to improve the user experience. I noted that the query behind one of the forms opened "OK" but scrolling thru the records was a little sluggish. The form itself is a

sp_executesql

2011-01-28T22:19:00.012-07:00

The system stored procedure sp_executesql can be used to run dynamic sql. It also provides a way to use input and/or output parameters. However, the BOL examples seemed to be mighty complicated so I put this together for a quick reference.For the example, we have a simple table "Cities" like this:City nvarchar(50),ST nvarchar(2),County nvarchar(50)If all you want to do is to look up the County

SSRS and CSV format

2010-12-18T23:24:00.003-07:00

Using SQL Server 2005 Reporting Services, I created a timed subscription to save a report as a CSV file to a Windows file share. However, the user was unable to import the file into their salesforce.com application.Turns out that the default export format for CSV uses Unicode encoding, so if you open the file with Excel, each row of the data ends up in the first column.Luckily SSRS has a file

Resize query columns

2010-08-28T10:06:00.009-07:00

My MS-Access application has several buttons that display a query in datasheet view, so users can sort, filter or export the results. I'm frequently disappointed in the appearance when the columns are either too narrow or wide; of course you can easily resize columns with a double-click, but Access doesn't work the same as Excel, i.e. Access considers only the visible rows when it resizes.I

Stored procedure text

2010-08-19T04:56:00.005-07:00

SQL Server has a system view sys.syscomments that holds the sql text for all of your stored procedures, views, triggers, UDF's, default constraints and computed columns.I was considering using BULK INSERT to import a file but hadn't used it for a couple years, so I wanted to search some old scripts to get the basic syntax. I Google'd "SQL Server stored procedure text" and found a short article by

Drop temp table

2010-08-19T04:33:00.002-07:00

By definition, when a batch completes in SQL Server, any local temp tables created are automatically dropped. So then, if your stored procedure creates a local temp table such as #temp it will be dropped when the sp completes, and you don't need to include the statement DROP TABLE #temp to release resources in tempdb.However, when you are developing a stored procedure, and the batch fails due to

ODBC without a DSN

2010-06-02T01:06:00.022-07:00

For years, I have been using MS-Access to connect to SQL Server databases using DSN's, and the process has always been painful & frustrating: do you create a User, System, or File DSN? Each has its advantages, but User / System requires a visit to each PC to config the DSN; In some cases having to log in with Admin credentials to make that happen. And a File DSN sits out there somewhere on the

Office 2010, part 3

2010-04-04T06:38:00.004-07:00

In the Access 2010 Beta, if you open a file from an Explorer window or a shortcut, you get the dreadedThe command or action '' isn't available now.But the app works perfectly after dismissing that message. This one had me stumped; tried decompile, compile & save, compact & repair; nothing fixed it. Luckily a few minutes of Googling revealed the answer: if you open that same file from within

Office 2010, part 2

2010-04-04T06:03:00.004-07:00

Today I decided to resolve the Access calendar issue, which is: I have a popup form that uses the Calendar 2.0 control, but Access 2010 does not support ActiveX controls. Yes, I know that starting with version 2007, Access provides a date picker icon that displays wherever you have a text box that has a date format (you can turn that off), but navigating is more cumbersome compared to the old

Scripted Mass Updates

2010-02-06T07:51:00.011-07:00

This article describes a method to generate scripts to perform mass-updates of data. The purpose is to document the updates that are done, for future reference.Let's say you have a table of customers, and each is assigned to a region based on the state being east or west of the Mississippi River. Therefore, Illinois is assigned to the East. However, in order to balance the workload, the users

Office 2010, part 1

2010-01-25T04:58:00.002-07:00

I have been working with the Office 2010 Beta for about a week now. I downloaded the Professional Plus version on January 17 and installed on my test PC which is an old Pentium III with 512 RAM running Windows Server 2003. The install was quite similar to the Office 2007 install.The program launches faster than 2007. Gone is the Office Button, in favor of a File tab on the ribbon which brings up

SQL Data Scripts

2009-11-09T05:19:00.003-07:00

A standard practice in SQL Server development is to make any changes to objects or data using a script and apply it to the DEV system; once verified, it is applied to QA; once verified, it is finally applied to production.You can take this one step further, and write your scripts to be deterministic - i.e. if the script is run multiple times, the outcome will be the same.For example, let's say

Reusable code

2009-11-04T16:49:00.003-07:00

My definition of reusable code: modular code that is flexible enough that it can be used in more than one way.For example, let's say that you have a table Orders like this:ID int identity,OrderDate datetime,Quantity intand you want the total orders for one year. Fine. All you need is something like this:CREATE PROC Test(@Year int)ASSELECT SUM(Quantity) AS TotalFROM OrdersWHEREYEAR(OrderDate) = @

Tally tables

2009-10-24T05:48:00.010-07:00

I first learned about tally tables from the forums at SqlServerCentral which is great resource for information about SQL Server. (Free registration is required to view the content.)A tally table is simply a single-column table that holds integers from 1 to any arbitrary large number. For example, this code from SSC will create a tally table of numbers from 1 to 11,000:SELECT TOP 11000 IDENTITY(

SQL Lookups

2009-10-15T21:09:00.004-07:00

I am working with an Access app that was converted to use a SQL Server database, and gradually replacing Access queries with T-SQL or stored procedures for better performance.If you have used Access for a while, you know that the so-called Domain Aggregate functions such as DCount, DLookup, and DSum are very expensive in terms of resource usage. Even if you write your own VBA function to perform

DATEPART and DATEFIRST

2009-09-25T18:55:00.008-07:00

I am working on a query that groups data by the week-ending date.In MS-Access, you can use DatePart to get the day of the week ( 1 to 7 ) and then add 6 thru 0 days to your test date to calculate the week-ending date. For a normal Sunday - Saturday workweek, if your date field is TheDate you would use this:DateAdd("d", 7 - DatePart("w", TheDate), TheDate)...and if your workweek if different, e.g.

SSMS - INSERT script

2009-08-27T04:48:00.002-07:00

SSMS allows you to quickly generate table scripts, such as INSERT, SELECT, and UPDATE, which can save lots of time when writing T-SQL.However, I noticed that if you have an IDENTITY column the INSERT script will leave that out.Granted, most of the time you won't be inserting a value into that column, and in fact you need SET IDENTITY_INSERT MyTable ON if you do wish to insert into that column.

SQL script encoding

2009-08-09T07:13:00.002-07:00

During development work with SQL Server, I will generally write a series of change scripts that need to be applied in a specific order. For example, one script might add a new table and another creates a stored procedure that uses that new table. Therefore, when I name the scripts I will prefix them with a sequential number e.g. 01_tblCustomer.sql, 02_prcGetCustomer.sql and so forth. This has

Pass-Thru Solutions

2009-07-19T06:59:00.003-07:00

Six months ago we converted an Access 2003 application to use MSSQL 2005 as the database, and are gradually converting reports to use pass-thru queries, with either T-SQL or stored procedures, to take advantage of the speed & power of the SQL Server.There are many complex reports, some of which have embedded summary subreports, and therein lies the snag - Access does not allow you to use a stored

Using IF EXISTS

2009-06-27T08:30:00.004-07:00

When I work with stored procedures, I will always test if the procedure exists, drop if it does exist, and then create the stored procedure. This produces nice clean code which can be saved to source control & run at any time as needed. Note that the create code includes the grant permissions.I had been using "the long version" of the IF EXISTS statement, for example:IF EXISTS (SELECT * FROM

External Change Tracking

2009-06-20T07:14:00.002-07:00

This article relates to my previous post that describes a change-tracking system for MS-Access. You can view the complete code with comments here: Change Tracking in AccessIn a very active database, the tracking table can become quite large, so here are two methods to move the tracking table to a separate file.Remember - always make a backup copy before making significant changes to your

Abort SQL batch

2009-06-14T07:03:00.002-07:00

I am working in an environment that has three databases:database ABC on server ABC - the production systemdatabase ABC_QA on server ABC2 - for QA testingdatabase ABC_DEV on server ABC3 - for developmentThe developer writes scripts to create / alter objects & manipulate data during development on ABC_DEV, and then sends them to the DBA to run against ABC_QA for QA testing. When QA is complete, the

SQL templates

2009-05-10T00:26:00.003-07:00

SSMS has a very handy feature in the Template Explorer (Ctrl-Alt-T) that contains 100's of templates for all sorts of scripting tasks you might require:Open the Template ExplorerDrill-down to the categoryRight-click on the desired task and click Open.Press Ctrl-Shift-M to display a popup screen & enter your parametersClick OKYou can also create custom templates:Right-click on the top node, select

Remote Queries in Access

2009-05-02T05:36:00.002-07:00

MS-Access makes it very easy to link to tables in other Access databases . . .File ~ Get External Data ~ Link Tables...just navigate to your mdb file & click Link, select the table(s) and click OK.But you can also connect & retrieve data from another file, without creating a linked table. This little-known technique has been around AFAIK since the Access 2.0 days. I'm not suggesting that you use

Change Management

2009-04-26T06:00:00.002-07:00

Managing change in SQL Server applications requires a process that protects data integrity & maximizes production uptime, while providing flexibility for new development. I would propose the following as a minimum configuration to accomplish these goals.Create 3 database environments -- one for production, one for development, one for QA. Only the DBA has direct access to production or QA. The

SQL scripts without sp_executesql

2009-04-11T08:32:00.002-07:00

There is a totally useless scripting option in SSMS 2005 that you think might mimic the behavior of EM 2000 but it does quite the contrary.Using Enterprise Manager in MSSQL 2000 you could generate a script to DROP and / or CREATE an object. The DROP statement used the "if exists" syntax, so you could replace an object with a new version. If you wanted to change an object (except for tables) you

SQL - list columns in a table

2009-04-04T11:07:00.004-07:00

I have gotten into the habit of using either sp_help or sp_columns to view a list of column names, for examplesp_help 'MyTable'orsp_columns 'MyTable'which are much faster than using the Object Explorer in SSMS to drill-down thru the database, table and finally the columns. The downside of these sp's is that they display much more info than usually needed; I just want a list of columns & data

Linking Access to SQL Server

2009-04-01T17:03:00.003-07:00

I have an Access app that links to SQL Server, and when I tried to attach a new SQL table in code I ran into a snag - the linked table would forget the user name & password, so Access would pop up one of those login dialog boxes.I compared the value of the table Attributes (a long integer) & discovered that my new table was different. A quick trip to Access Help was truly helpful - I was missing

Loop thru SQL tables

2009-03-27T06:53:00.004-07:00

"How can I loop through all the tables in a SQL Server database, and run T-SQL on each table where the name matches a pattern, without using cursors?"This question was recently posted at a SQL forum. Here is the solution. . .SQL Server has a system stored procedure sp_tables that displays all the tables in the database, and has an optional parameter that allows filtering the results. For example,

Tracking Access Button Clicks

2009-03-13T16:48:00.005-07:00

I am working with an older Access application that has almost 500 command buttons, and our task is to determine which features are still being used & which ones can be retired. Hopefully we'll recover screen space, plus improve the maintenance by removing obsolete queries & reports.Of course, asking the users which features they are using is one option, but with over 100 users that is not really

Access subform VBA

2009-03-13T07:13:00.007-07:00

Forms behave differently when they are used as subforms, and this can be a challenge when using VBA. Normally, when you load a form, Access adds it to the Forms collection. So if you open a form MyForm thenForms.Count = 1and you can refer to a control on that form using:Forms!MyForm!MyControlHowever, if that form contains a subform, MySubform, thenForms.Count = 1and this will fail:Forms!MySubform

Compact and Repair Database

2009-02-28T06:12:00.005-07:00

MS-Access files tend to grow over time, for a variety of reasons such asadding many new recordslarge number of user editsrunning action queries (insert, update, delete)design changes, especially to forms & reportstemp tables that are emptied & repopulatedusing the Name AutoCorrect optionsAccess replicationWorkGroup Security with encryptionThe theoretical design limit of an Access mdb file is 2 GB

Access replicas and runtimes

2009-02-15T08:03:00.004-07:00

We recently completed a lengthy project, migrating a replicated MS-Access 2003 application to a client/server model using Access as the front-end and SQL Server 2005 as the database. The application had previously been modified as necessary to utilize the Access 2003 runtime on terminal server.There are a few features in the app that display query results in a datasheet view, and every one of

Identity Crisis, part 2

2009-02-05T18:40:00.002-07:00

Talk about being lucky.We reviewed the tables that were almost out of headroom as far as PK identity values hitting the limit of an int, and found that for the two tables that were almost out of room, the PK's were not FK's but were simply in there to satisfy the basic rule that every table should have a primary key.These tables had only 20-25,000 records; it was only the fact that they were

Identity Crisis

2009-02-02T16:41:00.004-07:00

I am working with a SQL Server 2005 database that was upsized from a replicated Access 2003 database. We used the SQL Server Migration Assistant for Access 2003.Because the Access file was replicated, the Autonumber fields were set for Random, as opposed to Increment, so the resulting IDENTITY fields in SQL are spread across the range of an int, which is +/- 2 billion give or take. However, for

SSMA - the Memo bug - part 2

2009-01-31T08:27:00.003-07:00

My last post discussed a method for changing the data type of a field in SQL Server. My suggestion was to use the SSMS table designer to create the script for you, but there is a faster easier way provided that you can write some basic T-SQL.Keep in mind that the design requirement is to create a script to apply the change, because this same change needs to be applied to three different databases

SSMA - the Memo bug

2009-01-28T18:00:00.002-07:00

I'm working with the Microsoft SQL Server Migration Assistant to convert an Access 2003 db to SQL Server 2005. Just today I found another little bug.In Access you can have a Memo field that holds up to 64k characters, give or take. This is great for adding comments and notes.The SSMA converts those as nvarchar(max) and correctly moves the data, however when linking to it using the SQL Native

Time Sheets

2009-01-17T06:38:00.005-07:00

I am working on an MS-Access time & billing program for consultants. This is an old program that was originally designed in Access 2.0, and over the years it was converted to 97 and then 2000, and finally was split into separate front end / back end files.One of the new features requested is to allow the consultant to view their hours in comparison to the standard 8-hour day and 5-day week. For

SSMA bug watch

2008-11-29T06:47:00.002-07:00

The SQL Server 2005 Developer Edition installed on my Windows XP SP3 laptop had crashed over the weekend, so after a fresh reinstall including SP2 with hotfixes up to version 3073, I decided to re-run my SQL Server Migration Assistant for Access (SSMA) to create a new conversion file for testing.The application performance was extremely poor, so I tried running SQL Profiler to try to find the

Max Locks Per File

2008-11-18T16:28:00.002-07:00

I have a replicated MS-Access database that is used to store archive data from the production system. This archive is Access 2002 format and is approx 140 MB in size.The design master was recovered from a replica & saved to a local C: drive, and then a new replica was created & published to a network share. So far, so good.However, when I attempted to sync the two files, I received an error that

Excel prompts to save when no changes made

2008-11-13T16:55:00.004-07:00

I have a large Excel workbook that is a catch-all for budgeting, time analysis & other miscellaneous items. When I would open the file to look at something, not change anything but simply close it, Excel would prompt me to save changes?I found this article that explains "volatile formulas" in Excel such as CELL(), NOW(), RAND(), TODAY() and a few others. Here is the full article:"Save changes in

Sharing Linux folders for Windows

2008-10-05T10:09:00.002-07:00

Sharing Windows folders for access by Ubuntu Linux is fairly straightforward using Samba (SMB) but going in the opposite direction had me stumped.From Windows, I would go into My Network Places ~ Entire Network ~ Microsoft Windows Network ~ Workgroup and I could see the Linux machine. Then I would double-click on it and enter my Linux name and password, but it would not connect.The solution is

Replicated Queries

2008-09-05T04:59:00.004-07:00

MS-Access makes it very easy to change the SQL of a query simply by using a statement likeCurrentDb.QueryDefs("MyQuery").SQL = "SELECT * FROM MyTable"there is no need to Dim or Set anything, simply plug in the SQL and you're ready to go.However, I just discovered that this does not work in a replicated database. Or I should say, it works in the Design Master but it does not work in a Replica. I

Moving Days

2008-07-20T07:27:00.005-07:00

{EDIT} Around about September 2008, I decided not to move articles off this blog after all. I might copy a few to the tips page at my Web site, but otherwise I'm going to keep this blog intact. {EDIT}When I started this blog three years ago, I had intended to use it as a quick way to publish my technology findings both for the general Internet community, and also to use it as a knowledge base for

Batch Files

2008-07-06T06:10:00.004-07:00

By all accounts, DOS is dead. But since cmd.exe is still part of Windows, you can still write DOS batch programs to automate certain tasks.For example, I am working with an Access application that is rebuilt every month. The previous process was to have the user navigate to a shared drive and use Windows Explorer to copy a new version of the MDB file and then paste it to a specific folder on

Removing Access Replication

2008-07-04T08:56:00.003-07:00

Using replication with MS-Access adds numerous system fields and tables, and combined with the replication-tracking system, a replicated Access database can grow to an unwieldy size, especially if you have many users and/or there are frequent changes in the design master.The customary solution is to rebuild the database, i.e. to create a new non-replicated file and then use that to create a new

SSMA, part 3

2008-06-21T07:37:00.004-07:00

My previous posts about the SQL Migration Assistant for Access reported generally favorable results with this product which is used to migrate Access data into SQL Server. It does a remarkably better job than the old Access Upsizing Wizard which very often failed, especially when dealing with date fields. When SSMA has completed its work, the system is mostly operational.However, there are two

Access Replication

2008-06-08T08:04:00.003-07:00

Replication in Access provides a method of allowing remote users to work with a local copy of the database while disconnected from the network, and then synchronizing their changes when they do connect. This feature has been part of Access since the 90's but sadly, although Access 2007 supports it if you stay with the .mdb file format, if you choose to convert to the new .accdb format this

SSMA, part 2

2008-05-24T09:32:00.002-07:00

The SQL Server Migration Assistant for Access does a fine job in converting Access data into a SQL Server database. But there are a few issues.One issue is that SSMA does not convert relationships where the PrimaryKey in Access is a compound index, i.e. if your PK uses more than one field, that relationship will not migrate so you'll have to create that manually.Another issue is that SSMA does

SQL Server Migration Assistant

2008-04-27T15:01:00.003-07:00

"Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access 97 through Access 2003 to Microsoft SQL Server 2005. SSMA for Access converts Access database objects to SQL Server database objects, loads those objects into SQL Server, and then migrates data from Access to SQL Server." (from the readme file)This is an extremely useful tool, the

Office 2007 creates PDF's

2008-04-20T09:11:00.001-07:00

There is a significant new feature in Office 2007 that may encourage users to migrate to the 2007 version - the ability to create PDF files without requiring the full version of Adobe Acrobat. This is especially useful with Access 2007.In previous Office versions, you would need to install a PDF-creation program such as Acrobat, pdf995, or other program. Those programs generally install

Moving to Access 2007

2008-04-13T12:11:00.001-07:00

Office 2007 has generally received a lukewarm response, but mainstream support for Office 2003 is set to end in April 2009, so remember the Boy Scout motto of "Be Prepared".To date, I have had three customers that upgraded a single PC to 2007, while the rest of the users remained with 2003. Two have since downgraded back to 2003 due mostly to issues with Access 2007 compatibility. Some users

Vista and PCL6

2008-03-08T08:44:00.003-07:00

Vista continues to exhibit incompatibilities with peripherals, not just legacy devices but brand-new printers as well.I have an Access database that launches a mail-merge with Word (Office 2003 with SP3, running under Vista Business) and the customer is printing to a new HP color laserjet. Using the latest PCL6 driver, the first page prints correctly but then is followed by a printer error page

Printing from DOS

2008-02-23T08:39:00.002-07:00

Yes, hard to believe, but there are still some DOS applications out there in production environments.To redirect DOS printing to a network printer, open a command windowStart ~ Run ~ cmd {ENTER}and then run this commandnet use lpt1 \\servername\printername /persistent:yes

Access and mail merge

2008-02-14T15:17:00.005-07:00

Recently I've been running into some challenges using Access 2003 (2000 format) as the data source for mail merge, when using either Word or Publisher to produce merge documents.In either of those two programs, you select your Access file as the data source, and normally you're presented with a list of all tables and select queries in the database. But on several occasions, with different Access

Last Restore Details for SQL

2008-01-27T05:54:00.001-07:00

In a previous entry, I posted T-SQL code that will list the last restore dates for all db's on a SQL Server. The original purpose was to verify the dates that production backups were restored to the test environment.This enhanced script will also display the name of the MDF file and the name & location of the backup file that was used for the restore. This can serve as a double-check that the dev

Access File Size

2008-01-10T08:15:00.000-07:00

This function will return the size of the current Access database file. This function requires a reference to the Microsoft Scripting Runtime library, usually located at:C:\WINDOWS\system32\scrrun.dllAn Access mdb file can grow over time, especially if you have a lot of edits, empty / refill temp tables, and so forth. The purpose of this function is to get the current size, which (using other

Access 2007 problems

2007-12-28T06:54:00.000-07:00

I have an Access database that connects to SQL Server to provide custom reporting and batch updates of data. The file is 2000 format and was created using Access 2003 SP2.I'm having quite a few issues using this file in Access 2007. My first attempts at 2007 were calamitous due to code artifacts that would not compile (oops). I found and fixed things that 2003 ignores, and once it compiled I

Last Restore Date in MSSQL

2007-12-22T08:32:00.000-07:00

I regularly take backups of three databases from a production SQL Server 2005, and restore to as many as four different development servers, for testing with different OS's and hardware configurations. I created this procedure in the master database on each dev server that will display the last restore dates for all the databases:CREATE PROC dbo.procLastRestoreDatesASSELECT

Change Tracking in Access - complete

2007-12-21T07:35:00.000-07:00

This previous blog post has now been compiled into a single Web page, you can view the full article at my "Tips and Downloads" at this location:http://www.wvmitchell.com/tips/Change_Tracking_in_Access.html

Linking Access tables to SQL Server - III

2007-12-16T08:55:00.000-07:00

This is the final chapter on creating a linked server from SQL 2005 to Access 97. I posted this issue at the MS Newsgroups, and the bottom line is that since Access 97 is no longer supported by MS, there are few resources to resolve this problem - The MSSQL gurus don't have an explanation why it would or would not work, and they had no suggestions as to server configuration that might help.

Printing from Vista

2007-12-12T06:31:00.001-07:00

In a previous post, I described how Office 2003 , and especially Access, requires updated print drivers for Windows Vista. In some cases Access will not allow design or printing of any reports unless new Vista drivers are installed.I recently discovered that even Vista-compatible drivers can have issues - for example, an HP 1320 using the PCL 5 driver "works" with most apps, but Access reports

Last Modified Date

2007-12-08T20:18:00.001-07:00

I was looking for a way to display the last modified date on a form or report, but the two methods I had tried did not give the correct results.In this example, I was using a form named frmSwitchboard...Method 1SELECT DateUpdateFROM MSysObjectsWHERE Name="frmSwitchboard" AND Type=-32768Method 2CurrentDb.Containers("Forms").Documents("frmSwitchboard").LastUpdated..both of these returned the

Linking Access tables to SQL Server - II

2007-12-05T17:16:00.000-07:00

My previous post on this topic described creating a linked server to allow SQL Server to access data from a MS-Access database. A few more details are in order...SQL Server 2000 can link to either Access 97 or 2000 formats using the Jet 4.0 OLEDB provider, and can use either of these types of statements against your linked server, in this example AccessDB:SELECT * FROM AccessDB...

Working with Relations

2007-11-26T07:41:00.000-07:00

Relationships between tables are crucial to preserving data integrity in MS-Access or any other relational database system. In these examples, I show both how to create and delete a relationship. The "create" example creates a one-to-many relationship, enforces referential integrity, uses a single field, without cascades.You can certainly manage relations in Access using the GUI (Tools ~

Saving Access Forms as Reports

2007-11-21T17:26:00.000-07:00

Forms are the heart of an Access application, but sometimes a hard-copy is still required. A well-formatted form can be printed simply by using the DoCmd.PrintOut statement, but to print a single record you'll probably need to set a bookmark of some kind, apply a filter, print the form, and then use the bookmark to return to that same record.Access provides for a "Save As Report" feature, so you

Formatted MsgBox in Access

2007-11-10T09:16:00.002-07:00

I wanted to create a formatted message box in Access, with the first line being bold, to duplicate the built-in delete confirmation message. In older versions of Access, you could useMsgBox "First line is bold@Second line is normal@@"but when Access moved from 97 to 2000 this feature went away.I Google'd this & found several versions of a workaround, and here is what worked for me in Access 2003:

SQL Server Backup / Restore

2007-11-04T09:20:00.000-07:00

I routinely take a backup from the production SQL 2005 Server and restore it to my dev system. I had previously published my code for backup and restore, which used backup devices but I found that was not necessary.I have a folder on the production server named E:\SQL_Bill which is shared for me alone and is the target for my backup. I then zip and copy the file to my thumb drive, then copy and

ANSI Warnings and Vista

2007-10-17T07:34:00.000-07:00

I have an Access 2003 application that connects to a SQL Server 2005 database using pass-through queries that either contain T-SQL or execute stored procedures.When some of the user PC's were upgraded to Windows Vista, I noted that sometimes new tables were being created in Access with names like "Admin - 00", "Admin - 01" and so forth. Each table contained one to three records that held ANSI

Linking Access tables to SQL Server

2007-08-08T17:51:00.000-07:00

The question was, how do I link from SQL Server to an Access db and loop through all of the Access tables that end with "Data" ... My example uses a 2000-format file C:\BILL\Test2000.mdbFirst, run this one-time to create the linked server:sp_addlinkedserver@server = 'Test2000',@provider = 'Microsoft.Jet.OLEDB.4.0',@srvproduct = '',@datasrc = 'C:\BILL\Test2000.mdb'GOsp_addlinkedsrvlogin@rmtsrvname

AVG Free and Access

2007-07-26T08:06:00.000-07:00

AVG Free automatically scans MDB files when you open them, and this cannot be turned off in the Free edition. Even on a fast PC, it could take 20-30 seconds depending on the size of the database.This presents itself in two ways:1. You are using Access 2000-2003, just opened the db moments ago, and you want to do a quick compact & repair. Access tells you either "Cannot open database '(database

Tracking changes in Access

2007-07-15T13:30:00.002-07:00

Here is a method to track edits in MS-Access. The goal is to record the "who what when where" of edits made in an Access database. Since Access does not have triggers like SQL Server, we will be working at the Form level.NOTE - UPDATED 11/11/2007added table name to trackerTo keep things readable within the blogger format, you'll need to visit three links that will display details of what is

Error 3146: ODBC Call Failed

2007-06-23T10:36:00.000-07:00

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

Scripting T-SQL

2007-05-27T09:02:00.000-07:00

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.MyProcedureGOCREATE PROC dbo.MyProcedureAS-- actual code goes hereGOGRANT EXECUTE ON

Create SQL Server database query with MS Excel

2007-05-12T15:21:00.000-07:00

1. Start a new XL workbook2. Click Data ~ Import External Data ~ New Database Query3. 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 OK5. At the "Query Wizard" dialog, click Cancel6. At the "Do you want to continue editing this query in Microsoft Query?" click Yes.7. At the

Using BETWEEN with dates

2007-04-24T06:23:00.000-07:00

T-SQL has a handy BETWEEN statement that makes it easy to apply date range criteria. The basic usage isWHERE dtSomeDate BETWEEN '1/1/2007' and '1/31/2007'...which can replace the cumbersomeWHERE 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

Works with Vista

2007-04-07T09:07:00.000-07:00

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,

Blogger Support is Super

2006-04-14T13:11:00.000-07:00

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

Welcome

2005-01-17T19:53:00.000-07:00

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