change location where .adp creates DB on sql Server?

[BF]

Hello,

I have been experimenting with creating Sql Server DBs
from the Access .adp files. A problem that I am concerned
with is that there seems to be a default location where
the .adp creates the DB. In my case it creates all the
new DB files on the server computer at C:\Program
Files\Microsoft Sql Server\MSSQL\Data. The problem is
that this drive is pretty small compared to other drives
on the server where the main MDF files are stored. As my
place upgrades to Access 2002 from Access97, I could see
the C drive of the server getting inundated with a bunch
of new DBs from everyone's .adp's and thus choking the C
drive of the server. So, is there a way to change the
location where the .adp creates a new Sql DB? I did not
see any options for that on the create wizard. Maybe
there is a registry setting or something that could be
manipulated on each person's workstation?

Thanks,
BF

 

[Sylvain Lafontaine]

Your best option is to use SQL Server' Enterprise Manager to create the
database. You can buy the Developer Edition of SQL-Server for 50$.

Also, don't forget that the SQL-Server 2005 Express (free) will come out in
a few months (?). You can already play with the Beta version.

S. L.

 

[BF]

Thanks. I already have Sql Server 2000 Enterprise on a
200 gig server (2 cpu's). For some reason, I.T. made the
C drive only 12 gigs. So the main MDFs reside on the
other drive where there is plenty of room. Do you know if
there is a setting in EM where I can change the default
location where New DB's get created?

 

[Sylvain Lafontaine]

Yes, in the Database Settings for the Properties of the Server, you can set
the default location for new database.

If you use DDL for creating your databases, you can also override the
default location, if I remember correctly.

S. L.

 

[BF]

Yes, I found the settings in the
Server/Properties/Database Settings tab. I reset the
default setting, and if I create a new DB from Sql Server
EM, the new DB shows up in the new location. But if I
create a new DB from the Access.adp it still shows up on
the C drive.

May I ask how you can override the default settings for
location in the DDL?

 

[Sylvain Lafontaine]

Hum, you surprise me; I didn't think one second that ADP would also specify
the location for the database.

DDL is Data Definition Language: these are the statements that you use to
create a new database, a new table, etc. In your case, this is the « Create
Database » statement but you must be already logged into the SQL-Server to
use this.

A better option would be to use SQL-DMO. This is often the best thing to
use when you want to create a new database, detach/reattach one or make
backup/restore from Access. For example, see the end of the following
article for an example of creating a database with SQL-DMO:

http://msdn.microsoft.com/vstudio/downloads/addins/msde/databases.aspx

S. L.

 

[Sylvain Lafontaine]

See also the following article on CodeProject for other options:
http://www.codeproject.com/database/ConfigureMSDE.asp

S. L.

 

[BF]

This did work! Thanks very much for your help. It just
isn't quite as transparent as the .adp creating the adhoc
DB because now the users have to think a little bit (that
is supposed to be my burden . The users aren't really
Tsql People - don't think they want to take the time to
learn it. Might go as far as learning basic com ADO. I
think I will stick with your previous suggestion of just
creating individual DBs for the users and letting them
connect.

Many thanks again for your help.

 

[Sylvain Lafontaine]

Another suggestion: connect to no database (or to one of the 4 standards
SQL-Server database) and:
http://www.c-sharpcorner.com/Code/2004/May/CreatingDynamicDatabase.asp

S. L.

 

[BF]

I have a ton of ADO.Net code in VB.Net/C#/Aspx apps that
fortunately don't involve the endusers. The endusers just
want to be able to access the data that I pull/push
into/out of sql server with the .net apps with as little
intervention from me as possible. Access2002 looks like a
pretty good ticket.

Thanks for the reference. Do you know if there is an non-
beta release of Visual Studio 2005 out yet? I understand
it will contain sqlBulkData classes which will perform
like DTS (my apps push/pull gigs and gigs of data 24/7).

 

[Sylvain Lafontaine]

Non-beta release? The Beta 2 is not even out yet. Don't count on VS.NET
2005 RTM for at least 6 months but the next beta, in a few months, may have
a go live license.

S. L.

 

[BF]

Thanks. This is another good idea. I do, infact, use
sqldmo to list sp's on the sql server. I didn't think
about using it for create database. I guess, the enduser
could create a database and then connect to it with
the .adp file. My only other catch is to limit where the
end user can create the database. I can give them create
database rights, but is there a way to limit where on the
sql server machine they can create a db?

 

[Sylvain Lafontaine]

Sorry, I don't know.

The best way for you may be to use a stored procedure for creating the new
database and make the necessary verifications inside this SP.

S. L.

Thanks. This is another good idea. I do, infact, use
sqldmo to list sp's on the sql server. I didn't think
about using it for create database. I guess, the enduser
could create a database and then connect to it with
the .adp file. My only other catch is to limit where the
end user can create the database. I can give them create
database rights, but is there a way to limit where on the
sql server machine they can create a db?