I created a site that uses an Access database on the backend for orders,

a discussion board, and some other stuff.  The database has grown to

over 10 MB.  I have 2 questions.



1)  When deleting records from the database, the size does not get any

smaller.  New records add to the size.  The only way to shrink the

database is to delete old records and then compact the database.  For a

website, this means temporarily shutting down the site for maintenance

because the database must be downloaded, compacted, and then uploaded

again.  Is it possible to compact a database on the IIS server without

downloading the file to my local computer, maybe using ASP or something?



2)  I was thinking of converting to a SQL Server database, but I have no

experience with it and I don't have SQL Server in my development

environment.  Is it possible to convert an Access database to a SQL

Server database without having SQL Server on my local computer?  I

assume then that the connection string is all that would need to change,

but is that not the case?



Thanks for any input!

Regards,

Peter

I can not answer you on the first question but the second one is quite easy

to do:



1.  Make sure you are in a network environment where you can access the MS

Access Database over the network from SQL Server.

2.  Open the EnterPrice Manager in SQL Server

3.  Rightclick on the Database Node and select All Tasks

4.  From All Tasks select Import Data

5.  Follow the onscreen instructions



Regards

Helga



-----Original Message-----

From: Peter Foti (PeterF) [mailto:PeterF@S...]

Sent: Tuesday, November 27, 2001 5:39 PM

To: ASP Databases

Subject: [asp_databases] Access97 to SQL Server





I created a site that uses an Access database on the backend for orders,

a discussion board, and some other stuff.  The database has grown to

over 10 MB.  I have 2 questions.



1)  When deleting records from the database, the size does not get any

smaller.  New records add to the size.  The only way to shrink the

database is to delete old records and then compact the database.  For a

website, this means temporarily shutting down the site for maintenance

because the database must be downloaded, compacted, and then uploaded

again.  Is it possible to compact a database on the IIS server without

downloading the file to my local computer, maybe using ASP or something?



2)  I was thinking of converting to a SQL Server database, but I have no

experience with it and I don't have SQL Server in my development

environment.  Is it possible to convert an Access database to a SQL

Server database without having SQL Server on my local computer?  I

assume then that the connection string is all that would need to change,

but is that not the case?



Thanks for any input!

Regards,

Peter








$subst('Email.Unsub')



Read the future with ebooks at B&N

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks

The first question might be answered with the following...



Access (2000 anyway) has an option to compact the database automatically

after all

database connections have been closed. For compacting the database, you

still need

exclusive access to the database. What you would have when compacting

automatically,

is that you still have a maintenance period where no users can connect to

the database,

but you won't have to copy it to your localdrive, compact it and send it

back to the server.



The way to go is to do the following:



1) Open the Access database or Access project that you want Microsoft Access

to compact automatically.

2) On the Tools menu, click Options.

3) Click the General tab.

4) Select the Compact On Close check box.



I don't know if there's an ASP way of doing this, but to me it doesn't seem

like it.

Be free to correct me on this...



HTH,



Serge



----- Original Message -----

From: "Helga Y. Anagnostopoulos" <helga@k...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, November 27, 2001 4:44 PM

Subject: [asp_databases] RE: Access97 to SQL Server





> I can not answer you on the first question but the second one is quite

easy

> to do:

>

> 1.  Make sure you are in a network environment where you can access the MS

> Access Database over the network from SQL Server.

> 2.  Open the EnterPrice Manager in SQL Server

> 3.  Rightclick on the Database Node and select All Tasks

> 4.  From All Tasks select Import Data

> 5.  Follow the onscreen instructions

>

> Regards

> Helga

>

> -----Original Message-----

> From: Peter Foti (PeterF) [mailto:PeterF@S...]

> Sent: Tuesday, November 27, 2001 5:39 PM

> To: ASP Databases

> Subject: [asp_databases] Access97 to SQL Server

>

>

> I created a site that uses an Access database on the backend for orders,

> a discussion board, and some other stuff.  The database has grown to

> over 10 MB.  I have 2 questions.

>

> 1)  When deleting records from the database, the size does not get any

> smaller.  New records add to the size.  The only way to shrink the

> database is to delete old records and then compact the database.  For a

> website, this means temporarily shutting down the site for maintenance

> because the database must be downloaded, compacted, and then uploaded

> again.  Is it possible to compact a database on the IIS server without

> downloading the file to my local computer, maybe using ASP or something?

>

> 2)  I was thinking of converting to a SQL Server database, but I have no

> experience with it and I don't have SQL Server in my development

> environment.  Is it possible to convert an Access database to a SQL

> Server database without having SQL Server on my local computer?  I

> assume then that the connection string is all that would need to change,

> but is that not the case?

>

> Thanks for any input!

> Regards,

> Peter

>

>




> $subst('Email.Unsub')

>

> Read the future with ebooks at B&N

>

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

> =rn_ebooks

>

>

>




$subst('Email.Unsub')

>

> Read the future with ebooks at B&N

>

http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid

=rn_ebooks

You can compact it online if you use the .CompactDatabase method of the

JetEngine object:



Set objEngine = Server.CreateObject("JRO.JetEngine")

objEngine.CompactDatabase "...connstring1...", "...connstring2..."

Set objEngine = Nothing



(your database in connstring1 gets compacted and placed in connstring2,

where both connstrings are the usually OLEDB connection string)



If there are no errors, then you could copy the file in connstring2 over the

file in connstring1

Also, if you're using Access97, you might want to look at the code here:

http://www.aspalliance.com/mbrink1111/compact.asp

You need to specify the Jet version number so that you don't end up

compacting it and upgrading it to v4 at the same time.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Peter Foti (PeterF)" <PeterF@S...>

Subject: [asp_databases] Access97 to SQL Server





: I created a site that uses an Access database on the backend for orders,

: a discussion board, and some other stuff.  The database has grown to

: over 10 MB.  I have 2 questions.

:

: 1)  When deleting records from the database, the size does not get any

: smaller.  New records add to the size.  The only way to shrink the

: database is to delete old records and then compact the database.  For a

: website, this means temporarily shutting down the site for maintenance

: because the database must be downloaded, compacted, and then uploaded

: again.  Is it possible to compact a database on the IIS server without

: downloading the file to my local computer, maybe using ASP or something?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> -----Original Message-----

> From: Peter Foti (PeterF) [mailto:PeterF@S...]

> Sent: Tuesday, November 27, 2001 10:39 AM

> To: ASP Databases

> Subject: [asp_databases] Access97 to SQL Server

>

>

> I created a site that uses an Access database on the backend for orders,

> a discussion board, and some other stuff.  The database has grown to

> over 10 MB.  I have 2 questions.

>

> 1)  When deleting records from the database, the size does not get any

> smaller.  New records add to the size.  The only way to shrink the

> database is to delete old records and then compact the database.  For a

> website, this means temporarily shutting down the site for maintenance

> because the database must be downloaded, compacted, and then uploaded

> again.  Is it possible to compact a database on the IIS server without

> downloading the file to my local computer, maybe using ASP or something?



Yes you can do this via ASP... check MSDN for information on using JRO (Jet

Replication Objects) on the server. You can then call the ASP page or WSCRIPT

by using the AT command on SQL AGENT if they have than installed.



>

> 2)  I was thinking of converting to a SQL Server database, but I have no

> experience with it and I don't have SQL Server in my development

> environment.  Is it possible to convert an Access database to a SQL

> Server database without having SQL Server on my local computer?  I

> assume then that the connection string is all that would need to change,

> but is that not the case?



No, you will need SQL installed.  BUT you can use the Microsoft Data Engine

(MSDE) on your box to do the conversion.  It is free and supports up to 5

users, but it *is* SQL server.  You then transfer the .MDB file to the hosting

SQL Server, change the connection string, and Bob's your uncle....



Check MSDN for more info on MSDE.



Hope that helps.



-- Tomm