Hands on: GUID and bad

Solving the problem
There are (at least) two possible answers to John’s problem. He could write an Append query to add the rows to the new table. If he wanted to move just a subset of them, he could also use a query that identifies the rows making up the subset he wishes to append. If all the required records were for departments to do with furniture and/or furnishings, he could use an append query such as thi s:

INSERT INTO DeptsCopy ( DeptID, DeptName )

SELECT Depts.DeptID, Depts.DeptName

FROM Depts

WHERE ((Depts.DeptName) Like “*furn*”) OR ((Depts.DeptName) Like “furn*”);

The DeptsCopy table shows the result once the query has been run. If you’ve bought the DVD edition of PCW you’ll find the sample database DBCOct07.MDB on the cover disc. It’s also on our website. Here you’ll see that the Append query has not been run so the DeptsCopy table is empty.

John could also copy the data and structure of the original table at the same time and then delete the rows he didn’t want. Again, this would be easiest if only a few rows need to be deleted, or if the relevant records could be identified readily with a query – a Delete query.

Alternatively, John could change the field type from Autonumber/Replication ID to Number/Replication ID and create a structure-only copy of the table. Copying rows now transfers the GUIDs perfectly, but the Number/Replication ID setting cannot be changed back to the Autonumber/Replication ID, so the structure of the original table is lost.

Upsizing Access to SQL Server
It’s the same old story. You develop an Access database for a firm that swears it will never need more than three concurrent users, or to store more than 50,000 records. Three years later, it’s running like a dog for 20 concurrent users accessing two million records and it’s all your fault. Oh, and the database has become mission critical to the firm and runs 24/7. Guess who is to blame if any data is ever lost?

I love Access, but these new requirements fall outside its capabilities, so the best solution is to upsize the entire database to an engine that can support many users, has full logging capabilities and allows online backups. SQL Server is the obvious candidate: you can upsize the tables to the new engine while leaving the application as an Access database. You can continue to use the same user interface, but with a more robust database engine.

You can perform this upsizing by hand, but I strongly advise looking at some of the tools available. One, SSMA (SQL Server Migration Assistant), is free for download from Microsoft’s site.

The first version of SSMA was very buggy. Given that both Access and SQL Server belong to Microsoft, it was so bad as to be embarrassing. The Access query builder uses double quotes to delimit text, as in:

SELECT Customer.CustomerID, Customer.CustomerName

FROM Customer

WHERE Customer.CustomerName)=”Sally”;