Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

"Create Table" query for MS Access Databases

Posted on 2004-09-02
12
Medium Priority
?
376 Views
Last Modified: 2008-03-17
Hi again,

    I've two questions to ask

1 - Can we use the use the "Create table" query to make new tables within our database? Make sure I'm talking about MS Access Database. If we can, then what syntax shall be used to create table n how the datatypes must be defined. Moreover, if I use a field that receives the input string as Integer and it gets out of the integer range, then how we shall arrange the field. In Access we can simply do that by setting the "Field Size" to double n add the no. of decimal places. Hw it shall be used while creating a data field?


2 - Secondly, if the database is already ready, but missed to add one more field within it, then what syntax shall I use to add the missing field in the already exisiting database? Lets say I made a database named "Profile.mdb" containing "FName", "Lname" as basic fields but I need to add one more field within it using the online script. Then I definitely won't update the databse back again to overwrite the past one. What alternate shall be used instead of uploading to add our required fields within the exiting n uploaded database?

           Will wait for ur replies. Bye
0
Comment
Question by:adnan_from_hiit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
12 Comments
 
LVL 3

Assisted Solution

by:Ayesha_K
Ayesha_K earned 100 total points
ID: 11962502
to run queries go to Query tab in access ... click on New ... on the top of the NEW window right click ... and click on SQL View ... paste these queries there one by one ... and click on the red ! icon at the top ...

1- create table TestTable (TestInt integer Primary Key, testCharacter varchar(50));

2- alter table TestTable
add column TestNewColumn varchar(30)
0
 

Author Comment

by:adnan_from_hiit
ID: 11962647
thanx Ayesha n what abt creating field accept integer value as Double(Field Size)
0
 
LVL 3

Expert Comment

by:Ayesha_K
ID: 11962690
2- alter table TestTable
add column TestNewColumn1 float
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:adnan_from_hiit
ID: 11963030
thanx once again... any idea of renaming the existing data field within a table and to rename a table
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11963134
You could open access, single click on the table name TWICE and rename it.

also select the table and click Design ... and you can add a variable or edit the variable names / types.

Make sure that if you edit the variable type, you may lose data.

:) SD
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11963160
Here is more info if you need to Alter the table.

ALTER TABLE table
{ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field | CONSTRAINT indexname} }

http://www.devguru.com/Technologies/jetsql/quickref/alter_table.html
0
 

Author Comment

by:adnan_from_hiit
ID: 11963165
I'm asking to change it using online script not manually

I'm already aware of that

I've tried RENAME TABLE F1 TO F2

It changes the table name

what to do to change the data field in existing table
0
 

Author Comment

by:adnan_from_hiit
ID: 11963181
any idea ????
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11963202
One way to rename a column in Access is ...

sql = "ALTER TABLE MyTable ADD NewName VARCHAR(50)"

sql = "UPDATE MyTable SET NewName = OldName"

sql = "ALTER TABLE MyTable DROP OldName"

Coz "rename column" doesnt work in Access

Also here is more info on renaming tables. http://www.4guysfromrolla.com/webtech/tips/t030802-1.shtml
0
 
LVL 11

Accepted Solution

by:
sciber_dude earned 100 total points
ID: 11963252
This changes the variable type of the column

ALTER TABLE tablename MODIFY (
          columname type(length)
) ;

0
 
LVL 3

Expert Comment

by:Ayesha_K
ID: 11963359
The clauses to rename columns and tables are Postgres extensions from SQL92.  

that is why we can not simply rename tables and columns through the rename command as we do in SQL Server or oracle.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question