Suggestions for creating new SQL Server backend from code

[Beowulf]

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?

[Anthony England]

"Beowulf" <be*****************@hotmail.com> wrote in message
news:hUvCf.151$Cf7.56@trnddc06...

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?

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.

[Beowulf]

Anthony England wrote:

"Beowulf" <be*****************@hotmail.com> wrote in message
news:hUvCf.151$Cf7.56@trnddc06...

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?
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.

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
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.

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.

[Lyle Fairfield]

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.

[Beowulf]

Lyle Fairfield wrote:

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)
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.

[Lyle Fairfield]

Beowulf wrote:

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.

[Anthony England]

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Beowulf wrote:

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.

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....ab321f52403115
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.