Changing design of a live Access database

[bebemau]

Hello all,

How do you guys(those who use Access db as backend) implement change to the live Access database? Lets say I want to add a field to a table, since the access db on the web server has all the live data, so I cant just ftp my updated copy on my development machine to the webserver because I will overwrite live data. How should it be done? Any advice?

Thanks.

[steurm]

I use the first two options stated by Bruce. If the database is rather big, so that it takes a long time to upload, and the number of changes is not to high, I use SQL-statements to implement changes in db-design. In other cases I download / change / upload the database. In that case you have immediately a back-up as well

--
Steurm
www.steurm.net/steurm

[bebemau]

Thanks guys for the reply.

Honestly I am not too familiar with SQL server, I have worked with it but I am just not as good with it as with Access.

One PITA about Access is the ldb file, I dont know what is the default but it takes a while for it to be unlocked. And when its locked, I can do nothing with the mdb file.

So at this point I am not ruling out the possibility of using SQL server. Maybe you guys can help with some questions I have:
1. I dont have sql server at home on my development machine. Will I be able to connect to DASP's SQL server remotely if I install the clients on my machine?
2. I know sql server database is an add-on feature. How many database can I create by paying the $10/month? Also is it a feature I can remove if I decide I want to go back to Access?

[bruce]

The simplest way is to

1) stop your application
2) download the live database
3) make the change to the DB
4) reupload the new version

As a alternative, you can construct SQL query to change the databse structure.

Better yet, switch to MS SQL.

[b]quote:Originally posted by bebemau

Hello all,

How do you guys(those who use Access db as backend) implement change to the live Access database? Lets say I want to add a field to a table, since the access db on the web server has all the live data, so I cant just ftp my updated copy on my development machine to the webserver because I will overwrite live data. How should it be done? Any advice?

Thanks.
</blockquote id="quote"></font id="quote">

[bruce]

Yes. Access gets locked when your application is using it. Make sure you close all database connections properly in your application.

1) See
http://kb.discountasp.net/article.aspx?id=10064

or use MSDE manager (much smaller download but cost you a little bit)

http://www.discountasp.net/sp_msdemanager.aspx

2) With MS SQL addon, you only get 1 database. You can have as many tables as you wish.

You can cancel the addon at anytime. Your account will be refunded the unused amount.

[b]quote:Originally posted by bebemau

Thanks guys for the reply.

Honestly I am not too familiar with SQL server, I have worked with it but I am just not as good with it as with Access.

One PITA about Access is the ldb file, I dont know what is the default but it takes a while for it to be unlocked. And when its locked, I can do nothing with the mdb file.

So at this point I am not ruling out the possibility of using SQL server. Maybe you guys can help with some questions I have:
1. I dont have sql server at home on my development machine. Will I be able to connect to DASP's SQL server remotely if I install the clients on my machine?
2. I know sql server database is an add-on feature. How many database can I create by paying the $10/month? Also is it a feature I can remove if I decide I want to go back to Access?


</blockquote id="quote"></font id="quote">

[bebemau]

Hi Bruce,

Yes I always close, dispose connections but still the ldb is always there.

Anyways, I am a little confused. I have not used msde before, I have heard of it but have not worked with it at all. So are you saying I can have MSDE installed on my home machine, the code I use to connect to MSDE will work fine when its connecting to SQL server after uploading to the webserver? Or are you saying I should ask for a MSDE database instead of using Access or Sql server at DASP?

After reading your post, I looked all over on my machine for MSDE, couldnt find it? I have .net framework and web matrix installed but I dont see MSDE. I even ran the framework and matrix installation again but I dont see where I can add it. Do you know where I should look?

Thanks so much, your post was very helpful [;)]


[b]quote:Originally posted by bruce

Yes. Access gets locked when your application is using it. Make sure you close all database connections properly in your application.

1) See
http://kb.discountasp.net/article.aspx?id=10064

or use MSDE manager (much smaller download but cost you a little bit)

http://www.discountasp.net/sp_msdemanager.aspx

2) With MS SQL addon, you only get 1 database. You can have as many tables as you wish.

You can cancel the addon at anytime. Your account will be refunded the unused amount.

[b]quote:Originally posted by bebemau

Thanks guys for the reply.

Honestly I am not too familiar with SQL server, I have worked with it but I am just not as good with it as with Access.

One PITA about Access is the ldb file, I dont know what is the default but it takes a while for it to be unlocked. And when its locked, I can do nothing with the mdb file.

So at this point I am not ruling out the possibility of using SQL server. Maybe you guys can help with some questions I have:
1. I dont have sql server at home on my development machine. Will I be able to connect to DASP's SQL server remotely if I install the clients on my machine?
2. I know sql server database is an add-on feature. How many database can I create by paying the $10/month? Also is it a feature I can remove if I decide I want to go back to Access?


</blockquote id="quote"></font id="quote">
</blockquote id="quote"></font id="quote">

[bruce]

Sorry for the confusion.

I was referring to "MSDE Manager" not "MSDE". MSDE Manager is a SQL client tool that allow you to connect to MSDE and MS SQL servers.


[b]quote:Originally posted by bebemau

Hi Bruce,

Yes I always close, dispose connections but still the ldb is always there.

Anyways, I am a little confused. I have not used msde before, I have heard of it but have not worked with it at all. So are you saying I can have MSDE installed on my home machine, the code I use to connect to MSDE will work fine when its connecting to SQL server after uploading to the webserver? Or are you saying I should ask for a MSDE database instead of using Access or Sql server at DASP?

After reading your post, I looked all over on my machine for MSDE, couldnt find it? I have .net framework and web matrix installed but I dont see MSDE. I even ran the framework and matrix installation again but I dont see where I can add it. Do you know where I should look?

Thanks so much, your post was very helpful [;)]


[b]quote:Originally posted by bruce

Yes. Access gets locked when your application is using it. Make sure you close all database connections properly in your application.

1) See
http://kb.discountasp.net/article.aspx?id=10064

or use MSDE manager (much smaller download but cost you a little bit)

http://www.discountasp.net/sp_msdemanager.aspx

2) With MS SQL addon, you only get 1 database. You can have as many tables as you wish.

You can cancel the addon at anytime. Your account will be refunded the unused amount.

[b]quote:Originally posted by bebemau

Thanks guys for the reply.

Honestly I am not too familiar with SQL server, I have worked with it but I am just not as good with it as with Access.

One PITA about Access is the ldb file, I dont know what is the default but it takes a while for it to be unlocked. And when its locked, I can do nothing with the mdb file.

So at this point I am not ruling out the possibility of using SQL server. Maybe you guys can help with some questions I have:
1. I dont have sql server at home on my development machine. Will I be able to connect to DASP's SQL server remotely if I install the clients on my machine?
2. I know sql server database is an add-on feature. How many database can I create by paying the $10/month? Also is it a feature I can remove if I decide I want to go back to Access?


</blockquote id="quote"></font id="quote">
</blockquote id="quote"></font id="quote">
</blockquote id="quote"></font id="quote">

[bruce]

Yes, MSDE is basically a toned down version of SQL

You can connect to a remote SQL server with no problem

[b]quote:Originally posted by bebemau

Oh I see, you are talking about the clients.

So is the code to connect to sql server the same as to msde? Using the same name space in .net, system.data.sqlClient can connect to msde also? If so, maybe thats what I can do since I should be able to install msde(if I can find it).

Oh, one question wasnt answered was whether I can connect remotely from home to DASP's sql server. I assume it can be done?

Thanks again.
</blockquote id="quote"></font id="quote">

[bebemau]

Oh I see, you are talking about the clients.

So is the code to connect to sql server the same as to msde? Using the same name space in .net, system.data.sqlClient can connect to msde also? If so, maybe thats what I can do since I should be able to install msde(if I can find it).

Oh, one question wasnt answered was whether I can connect remotely from home to DASP's sql server. I assume it can be done?

Thanks again.

[bebemau]

Great, thanks for all the help and info!!!

[b]quote:Originally posted by bruce

Yes, MSDE is basically a toned down version of SQL

You can connect to a remote SQL server with no problem

[b]quote:Originally posted by bebemau

Oh I see, you are talking about the clients.

So is the code to connect to sql server the same as to msde? Using the same name space in .net, system.data.sqlClient can connect to msde also? If so, maybe thats what I can do since I should be able to install msde(if I can find it).

Oh, one question wasnt answered was whether I can connect remotely from home to DASP's sql server. I assume it can be done?

Thanks again.
</blockquote id="quote"></font id="quote">
</blockquote id="quote"></font id="quote">

[duane]

beb,
Another alternative is this cool (and free!) application...

www.stpworks.com/DesktopDefault.aspx?tabindex=1&tabid=3&articleid=3 (http://www.stpworks.com/DesktopDefault.aspx?tabindex=1&tabid=3&articleid=3)

its called Stp database administrator. It is fairly easy to install and use.

[bebemau]

Thanks, thats a good piece of info!!