Access for a SQL Server Programmer

[IanStevenson]

Hi Folks,

I have been using SQL Server for years in web projects (C# mostly), usually using ADO in the "thinnest" possible way since I don't really trust it very much and I like to wrap my DBs in business objects.

I now find myself faced with the prospect of a project that will need me to use an Access DB on catalyst 2. I already have an ASP.NET 2.0 website up and running, but I need some help figuring out how to approach the Access side of things.

1) Designing the database. I would usually do this locally in the SQL Server tools or Visual Studio, then use the "create scripts" on the live server. What is the best way to do this with Access? Do I just design the DB in access/VS then upload the MDB File?

2) I usually manage the remote DB instance using the SQL Server Tools. It is possible to access an Access DB hosted at catalyst2 from my desktop? Or do I have to just use FTP to retrieve the file, or make the changes via my own web sites code?

3) What, if any, backups are kept at Catalyst2 of AccessDBs? Usually the SQLServer DBs I work with are backed up - do I have to take responsibility myself for backing up the AccessDB? Do I do this via FTP - just copy the file?

Sorry, these may seem like silly questions but its the sort of stuff that will take ages to figure out and I'm hoping one of you guys can set me straight in about 10 seconds :-)

Ian

[WhiskyFudge]

Note - Please do not use access for any site that is likely to have a high db hit rate. I'm saying this more for the sake of your site than I am for the sake of server stability (although the two or interlinked)

Moving on . . .

1. Design the database in access, upload the mdf file to the db folder of your site, create a DSN. You can then use this DSN as your connection string to the db using ADO.net in the same way you would with MS SQL.
2. Use the DSN to connect access to the database to manage it remotely
3. Access databases are not specifically backed up. They are however included with the backup that is made of your entire site.

If you need any more help or an explanation of any of the above let me know

[IanStevenson]

Thanks for that!

I've designed the site so that most of the data is read once and held in in the ASP.NET application cache. The Access DB will just be used for logging transactions, the secure part of which are handled by a third party provider. If I get to a high transaction rate (I wish...) I'll be able to justify switching to SQL Server without any probs...

1) Great - I can get the DSN working locally so I'm sure it will be fine when I deploy

2) I've tried fiddling, but I can't figure out how to connect Access to a remote access DB

3) I've realised I already have a solution to this...

Thanks.

Ian

[WhiskyFudge]

I dont have access 2003 installed on my machine so can't help you with the specifics. But when you create a new database there is an option to create from/using existing data or something like