Creating databases manually?

C

Cody

Hi,
I am suposed to create a database manually. By this I mean using SQL
statements. I am unable to figure out how this is done though. The only way I
can see how to create a database is to use the wizards and such in Access,
but I am not supposed to use that method. I am not seeing anywhere where SQL
statements can be entered. Or is this done in command prompt somehow? If it
makes any difference, I am using Access 2003.

Any help would be greatly appreciated!!
 
J

Jerry Whittle

I know how to do this in Oracle as you can have Oracle and SQL*Plus running
without a database open. Believe it or not the following will create an
Oracle database using defaults.

CREATE DATABASE;

Then on to things like creating tablespaces, users, tables, etc.

I don't have a clue how you'd do something similer inside Access as you have
to have a database open to get to a SQL window in the first place.
 
J

John W. Vinson

Hi,
I am suposed to create a database manually. By this I mean using SQL
statements. I am unable to figure out how this is done though. The only way I
can see how to create a database is to use the wizards and such in Access,
but I am not supposed to use that method. I am not seeing anywhere where SQL
statements can be entered. Or is this done in command prompt somehow? If it
makes any difference, I am using Access 2003.

Whoever assigned you this task apparently is not knowledgable about
Access, since neither Access SQL nor SQL/Server SQL has any commands
to create an Access Database container.

Are you sure that the assignment isn't to create a Table, rather than
a Database? An Access Database is the container file for multiple
tables, forms, reports, and queries - and you certainly *can* use a
Create Table query to create a Table within a database.

John W. Vinson [MVP]
 
K

Klatuu

Jerry is correct. I don't believe you can create a database without an
instance of Access open. Maybe this is a terminology issue. When you use
the word "database" do you mean create a new table in the database?
If so, you need to do look in the VBA Help file and find the information on
Data Definiition SQL. It will show examples on how you create a table,
define the fields, and the properties of the fields.
 
C

Cody

Sorry, I was in a bit of a rush. I meant to say table instead of database,
but I think I figured it out.

I went to "Create query in Design view". Then closed the "Show Table"
window. Then finally switched the view to "SQL View".

I'm assuming this is the way to do it, but if there are other methods to
create tables using SQL, let me know.

Thanks.
 
J

John W. Vinson

Sorry, I was in a bit of a rush. I meant to say table instead of database,
but I think I figured it out.

I went to "Create query in Design view". Then closed the "Show Table"
window. Then finally switched the view to "SQL View".

I'm assuming this is the way to do it, but if there are other methods to
create tables using SQL, let me know.

Well, what you describe will let you create a Query in SQL by typing
it in. If that query is a DDL Create Table query with the proper
syntax, it can be executed and will indeed create a table.

But that's a bit like saying that opening a New Document in Word is
how you can create an elegant sonnet. Sure, if you type it in right...

Have you looked at the online help for the queries as suggested?

John W. Vinson [MVP]
 
T

Tim Ferguson

I don't believe you can create a database without an
instance of Access open.

'
' do this in Excel or vbs or powerpoint etc etc
' the original of this was a working script but I've edited
' it here for clarity, so it's no longer guaranteed!!
'

Dim dbe ' As Object in VBA
Dim db ' As Object in VBA

Const newMDBFile = "r:\TempData\MyMDB.mdb"

Set dbe = CreateObject("DAO.DBEngine.36")

If Len(Dir(newMDBFile))>0 Then Kill strMDBFile

Set db = dbe.CreateDatabase(newMDBFile, _
";LANGID=0x0409;CP=1252;COUNTRY=0", _
64)

db.Execute _
"CREATE TABLE MyTable ( " & _
" MyCounter COUNTER PRIMARY KEY, " & _
" MyName TEXT(32) NULL, " & _
" MyBirthday DATETIME NOT NULL " & _
");", _
dbFailOnError

db.Execute _
"INSERT INTO MyTable (MyName, MyBirthday) " & _
"VALUES ( ""Tim"", #2008-12-25# );", _
dbFailOnError

db.Close


' Hope that helps


Tim F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top