Automating Access to SQL Server

We're trying to put a view of data maintained in desktop Access databases online and into SQL Server.
The desktop Access system uses separate databases instead of tables within one database, It's a strange design, but it can't be changed.
We have been importing all of the separate databases into a single, new Access database, then upsizing the new databse to SQL Server, then uploading it.
This is not going to work long term, because we are stuck with a 250 mB Access database to upsize and upload, when we never need to update more than 2 or 3 of the tables and upload more than 2 mB.
We'd like to be able to upload only the tables -- preferably the Access *.mdb's -- that have changed, and then replace the SQL Server tables with the new information. And we'd like to automate it as much as possible, without upsizing Wizardy.
I don't know where to even begin looking for information about how this might be done.
Any suggestions would be deeply appreciated.
- Tinker
This sounds possibly like a job for DTS. You could set up a DTSpackage on the SQL Server that will loop through the Access databasesand perform the updates as needed.
This site is a good resources for DTS work:http://www.sqldts.com.
|||Hi Terri, and thank you.
When I first ran across information about DTS, I skipped right past it. I'm curently out in the MS Tech Net learning more and it appears DTS may be much closer to what we want to do than I first thought.
Neither the FAQs athttp://www.sqldts.com/ nor the information I have trolled-through so far on the Tech Net discuss automating DTS; is that possible?
While we don't want to ride herd on these data updates any more than is absolutely necessary, it would be super great if I don't have to try to program my way through deleting all the records in an SQL table, then refilling the table with the new records...
Appreciate your suggestion,
- Tinker|||Sure, it's possible to automate DTS. You can use SQL Agent toschedule a DTS package to run on a schedule of your choosing.|||Thank you. I finally found where I get to do all this.
Sometimes I feel dumber than usual... 8-)
- Tinker

Automatic installation

I have a small desktop application that is distributed in CD.
The client installs the application without any attendance, therefore I need that everything is completely automatic.
Is it possible to install SqlServer Express in that way?

hi,

AFAIK, this kind of install requires you to launch the sqlexpr.exe boostrap installer of SQLExpress... you can eventually provide the required parameters as command line params or using an ini file... personally I do prefer the command line options as no sensible information is required to be stored in the provided ini file...

or, if your app is designed accordingly, you could even use ClickOnce deployment..

regards

|||

Also check out the FAQ at the top of this forum, one of the questions answered is about configuring SQL Express during installation and covers the most common of the required parameters that Andrea mentions in what I hope is a more friendly and understandable way than BOL does.

Mike

|||Thank you, I will check your advice.
An additional question: do you believe that SqlServer Express can work absolutely without any administration?
That is to say: my clients are people without any technical knowledge and they usually communicate never with me.
Is SSE the correct database for this status, or should I use a desktop database like VFP or Access?|||

hi,

personally I do not think there're DBMS out there not requiring any kind of periodical administration.. even Access requires, from time to time, to compact read/write databases.. and so does SQL Server/SQLExpress...

you can "automate" usual admin tasks as some sort of "maintenance plans" (not supported feature on SQLExpress becouse of the absence of the SQL Agent) via other schedulers, like the builtin OS scheduler to execute a Transact-SQL script(s) to reindex user's database(s), check db integrity and the like and, obviously, define a backup strategy to protect against disaster scenarios...

you can perhaps have a look at http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29 to get some start ideas..

personally I provide a set of Trasact-SQL scripts to be scheduled via the native OS scheduler to automate these tasks with an added feature.. I do provide a CLR assembly (you can get for free from my web site, amDBObj) to be "imported" as a CLR stored procedure to provide some sort of Database Mail missing feature, so that those scripts can notify me, the admins/it stuff that the task has failed...

regards

Automatic installation

I have a small desktop application that is distributed in CD.
The client installs the application without any attendance, therefore I need that everything is completely automatic.
Is it possible to install SqlServer Express in that way?

hi,

AFAIK, this kind of install requires you to launch the sqlexpr.exe boostrap installer of SQLExpress... you can eventually provide the required parameters as command line params or using an ini file... personally I do prefer the command line options as no sensible information is required to be stored in the provided ini file...

or, if your app is designed accordingly, you could even use ClickOnce deployment..

regards

|||

Also check out the FAQ at the top of this forum, one of the questions answered is about configuring SQL Express during installation and covers the most common of the required parameters that Andrea mentions in what I hope is a more friendly and understandable way than BOL does.

Mike

|||Thank you, I will check your advice.
An additional question: do you believe that SqlServer Express can work absolutely without any administration?
That is to say: my clients are people without any technical knowledge and they usually communicate never with me.
Is SSE the correct database for this status, or should I use a desktop database like VFP or Access?|||

hi,

personally I do not think there're DBMS out there not requiring any kind of periodical administration.. even Access requires, from time to time, to compact read/write databases.. and so does SQL Server/SQLExpress...

you can "automate" usual admin tasks as some sort of "maintenance plans" (not supported feature on SQLExpress becouse of the absence of the SQL Agent) via other schedulers, like the builtin OS scheduler to execute a Transact-SQL script(s) to reindex user's database(s), check db integrity and the like and, obviously, define a backup strategy to protect against disaster scenarios...

you can perhaps have a look at http://www.sqldbatips.com/showarticle.asp?ID=27 and http://www.sqldbatips.com/showarticle.asp?ID=29 to get some start ideas..

personally I provide a set of Trasact-SQL scripts to be scheduled via the native OS scheduler to automate these tasks with an added feature.. I do provide a CLR assembly (you can get for free from my web site, amDBObj) to be "imported" as a CLR stored procedure to provide some sort of Database Mail missing feature, so that those scripts can notify me, the admins/it stuff that the task has failed...

regards