Suggestions for creating new SQL Server backend from code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Beowulf - 27 Jan 2006 21:03 GMT

I'm working on upsizing an application from MS Access MDB -> MDB backend
to MS Access ADP -> SQL Server backend. The application has the ability
to create a new, empty database.

Right now all the code is generating DDL statements inside VBA and then
passing them as strings to an ADO command object. I'm going to have to
redo most of the code anyway to update it to work with SQL Server so I
wanted to think about redesigning the whole thing.

I've thought of 2 alternatives. One, having a protected folder in the
App.Path that contains a series of .SQL text files. The create new
function would then just read through each file in the folder and
execute the SQL statements it contains. Two, use SQL DMO objects to
create all the new tables.

Any other suggestions about good ways to keep this task modular and easy
to maintain?

Reply to this Message

Anthony England - 29 Jan 2006 10:28 GMT

> I'm working on upsizing an application from MS Access MDB -> MDB backend
> to MS Access ADP -> SQL Server backend. The application has the ability
[quoted text clipped - 13 lines]
> Any other suggestions about good ways to keep this task modular and easy
> to maintain?

You don't mention what benefit you might hope to gain from either of these
two different approaches. One involves extra text files and tryig to
protect them by puting them in a special folder and the other needs relies
on additional components being installed which may or may not be on the
client pc.
It would seem to me, that by having the actual commands in your vba coding,
you have tight control over the content and can use error handling to make
sure you know exactly what is happening. By all means you could cheat and
use Enterprise Manager to create the scripts, but I would incorporate them
in a vba module with a top level function such as Public Function
CreateBlankDb(strServer As String) As Boolean. The function would contain
other functions each returning True/False to indicate whether they were
successful - to create the tables, the indexes, relationships, etc.

Reply to this Message

Beowulf - 29 Jan 2006 15:24 GMT

>>I'm working on upsizing an application from MS Access MDB -> MDB backend
>>to MS Access ADP -> SQL Server backend. The application has the ability
[quoted text clipped - 19 lines]
> on additional components being installed which may or may not be on the
> client pc.

The benefit, as I see it, is that the load procedures would be basically
agnostic about the data structure. They just read a directory and do
what it says. Also, I wouldn't have to change code, recreate and
redistribute an ADE file if I make changes. I also already have a
number of SQL files that I've generated over the past month or so while
setting up the SQL Server database that I created from SQL Query Analyzer.

> It would seem to me, that by having the actual commands in your vba coding,
> you have tight control over the content and can use error handling to make
[quoted text clipped - 4 lines]
> other functions each returning True/False to indicate whether they were
> successful - to create the tables, the indexes, relationships, etc.

You're probably right. Given that if I am changing the structure of the
database, that I'm probably also going to need to make other changes
elsewhere in the app, the benefit I was hoping to gain from the SQL
Files method is probably illusory.

Reply to this Message

Lyle Fairfield - 29 Jan 2006 20:44 GMT

I assume you do know that you can import the tables into the ADP's
MS-SQL backend with no SQL at all:

DoCmd.TransferDatabase acImport, "Microsoft Access", "Northwind.mdb",
acTable, "Customers", "Customers", True

and that you can export them from the mdb to the ADP MS-SQL with no SQL
at all:

DoCmd.TransferDatabase acExport, "Microsoft Access", "FFDBABooks.adp",
acTable, "Employees", "Employees"
(Yes, [Microsoft Access] works with an ADP)

This way you do not need any pre-written SQL (You can use OpenSchema to
get a list of table names.)

I realize you may not get the column definitions as you want them. You
could upgrade those later.

Reply to this Message

Beowulf - 31 Jan 2006 01:21 GMT

> I assume you do know that you can import the tables into the ADP's
> MS-SQL backend with no SQL at all:
[quoted text clipped - 8 lines]
> acTable, "Employees", "Employees"
> (Yes, [Microsoft Access] works with an ADP)

I used Access's Upsizing Wizard to get the tables into my "seed"
database on SQL Server 2000 that I'm using for development testing. I'm
primarily concerned at the moment with translating a function of the
original application where it could create empty MDB files at the
request of the user.

This function no longer applies now that the backend is in SQL Server so
I'm exploring options for implementing the same functionality with the
new ADP front/SQL Server 2000 backend configuration.

> This way you do not need any pre-written SQL (You can use OpenSchema to
> get a list of table names.)
>
> I realize you may not get the column definitions as you want them. You
> could upgrade those later.

I wasn't aware of this capability of the TransferDatabase command. I
was only familiar with it as a tool for importing/exporting text. This
nugget of information may come in handy in the future regardless.
Thanks for your comments.

Reply to this Message

Lyle Fairfield - 31 Jan 2006 02:01 GMT

> I'm
> primarily concerned at the moment with translating a function of the
> original application where it could create empty MDB files at the
> request of the user.

>From an ADP connected to Database Temp on Server FFDBA\SQLExpress

I run this code

DoCmd.TransferSQLDatabase "FFDBA\SQLExpress", "Temp2", , , , False

A new copy (without data) of Temp called Temp2 is created on
FFDBA\SQLExpress.

Reply to this Message

Anthony England - 31 Jan 2006 07:14 GMT

>> I'm
>> primarily concerned at the moment with translating a function of the
[quoted text clipped - 9 lines]
> A new copy (without data) of Temp called Temp2 is created on
> FFDBA\SQLExpress.

Well you certainly can't get much quicker than that! I had never used this
as I don't have any adp's in production - but I just tried it and have still
not managed to make it work. The first error involved invalid user accounts
which were transferred from a previous domain and should have been deleted
anyway - so this was an easy fix. The second error was a bit more obscure
and I still need to fix it but it is related to this post:
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/
363cea40b18f6106/29ab321f52403115?lnk=st&q=Cannot+use+TEXTIMAGE_ON&rnum=1&hl=en#
29ab321f52403115
The third error is caused by running this code for a second time, which does
not allow you to delete the first copy and replace it with a second, so you
have to find another way to check and prompt whether to overwrite. With all
of these little things, you have no fine control over what happens and you
only have one line of code to succeed or fail, and if it does fail, you may
well be left with a partially completed database.

What this method does is to try and create a copy of what is currently there
and as I have found out, there may be objects present that shouldn't be
there for whatever reason. Perhaps some person or process has screwed
things up and you want to start again with a fresh database with objects as
they should be, not a copy of the current situation.

I suppose it's fairly obvious really: the built-in method is quick and easy
but offers no flexibility. Building you own function, is a lot more work
but you would have much tighter control over what happens.

Reply to this Message