Create Tables on SQL Server?

[Guest]

Can we use Access to create new tables in a MS SQL Server 2K database that we
already have a link to? If so, how do I go about doing that. I already have
dbo permissions on the SQL DB, so security will not be a problem. In my one
effort, the new table was created locally, not in the SQL2K DB. Since our
tables are 1 GB in size with 2 million rows, I don't want the data to be
stored locally. I guess to boil it down, how good is Access 2K as a front
end for the SQL Server DB? Can I create and drop tables in that DB as easily
as I can by using the Access .MDB structure?

 

[Kevin3NF]

While there may be a VBA code way to do it...I would always create my tables
in SQL Server directly, regardless of whether you are using MDB or ADP as
the front-end...

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

www.experts-exchange.com - experts compete for points to answer your
questions

 

[Guest]

I was trying to avoid installing Enterprise Manager on this user's PC. It
sounds as if Access is OK for getting and modifying data, but it cannot be
used easily for creating new SQL server tables, or for modifying the table
structure of existing tables? That also means that the DBA needs to be
involved in the indexing of new tables. The user is trying to get the ease
of use of Access on a larger shared database.

 

[Brendan Reynolds]

If you're looking for a point-and-click, GUI method of creating SQL Server
tables, you can do that with an Access ADP. I believe it would have to be
done programmatically with an MDB.

 

[Guest]

Right you are. Where do I find Access ADP? We are using Access 2000. Am I
confined to using passthrough queries to create and drop tables with that
version?

 

[Brendan Reynolds]

You don't 'find' an Access ADP, you create one.

In Access 2000 and later, there are two types of Access applications you can
create - MDBs and ADPs.

I'm using Access 2003. In Access 2003, when I select New from the File menu,
Access displays the New File task pane, and two of the options there are
'Project using existing data ...' and 'Project using new data ...'. These
are the options that create ADPs. The details may be different in Access
2000, but the options are there somewhere.

I don't use ADPs to create applications, but I do use them as a design tool
during development. They're not a complete replacement for Enterprise
Manager, but I do find some things are easier to do in an ADP. For example,
I might initially create a stored procedure in an ADP, and modify the SQL
later in Enterprise Manager.

 

[Albert D.Kallal]

Can I create and drop tables in that DB as easily
as I can by using the Access .MDB structure?

Sure, it is no more difficult to drop/create tables in VB, VB.net, or
ms-access.

You simply create a ADO connection object, and execute your sql.

This is standard fair in any programming system that you would use with sql
server.

I am not sure if you are asking if this is possible, or do you have take a
different approach in ms-access as compared to VB, or c++, or whatever you
are using (answer = they are all the same in this regards. This is one case
where using ADO over DAO is some what easier).

In ms-access, and a local table, and a standard mdb file, you can use:

CurrentProject.Connection.Execute "drop table Mytable"

For sql server, you obviously must have a connection object, so use

MyConnection.Execute "drop table mytable"

Both of the above simply delete a table.