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