Code to build a table in an access database

I am using VB.net code. I wan to create a table with 3 fields. Autonumber as autonumber(primarykey), Name as string(25 Characters), and Lastupdated(16 Characters) as string. Can some show me the simple sql string to produce this table?
0
tvb2727
8/16/2008 4:15:31 PM
asp.net.access-datasource 4679 articles. 0 followers. Follow

10 Replies
427 Views

Similar Articles

[PageSpeed] 27

Look at the SQL CREATE TABLE:

http://msdn.microsoft.com/en-us/library/aa258255(SQL.80).aspx

Like so:

CREATE TABLE [dbo].[Test](
 [autonumber] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](25) NULL,
 [LastUpdated] [varchar](16) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
 [autonumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


David Kiff
http://DavidKiff.co.uk

-- "Mark As Answer" If my reply helped you --
0
DavidKiff
8/16/2008 4:22:06 PM

The question is for an Access Database, not SQL server!

 Look at http://msdn.microsoft.com/en-us/library/aa140011.aspx#acfundsql_ddl

By the Way, Are you sure you want to be the field "LastUpdated" to be a string?

0
hans_v
8/16/2008 7:48:51 PM

CREATE TABLE [tablename]([autonumber] COUNTER NOT NULL, [name] TEXT(25) NOT NULL, [LastUpdated] DATETIME NOT NULL);

CREATE INDEX [PrimaryKey] ON [tablename]([autonumber]) WITH PRIMARY;

0
hans_v
8/16/2008 7:57:01 PM

A great tool to manage an Access database online is STP Database Administrator

0
hans_v
8/16/2008 7:59:54 PM

hans_v:
The question is for an Access Database, not SQL server!

Ahh I read the question not the title!

If a create table was required for access you can always use this:

CREATE TABLE Test(
 id COUNTER PRIMARY KEY,
 Name VARCHAR(25),
 LastUpdated VARCHAR(16)
 );


David Kiff
http://DavidKiff.co.uk

-- "Mark As Answer" If my reply helped you --
0
DavidKiff
8/16/2008 8:06:16 PM

I tried this code and i received a syntax error message in VB.net:

  

1    Dim setnew As String
2                setnew = "CREATE TABLE Test(id COUNTER PRIMARY KEY,Name VARCHAR(25),LastUpdated(VARCHAR(16))"
 
0
tvb2727
8/18/2008 6:48:14 PM

NAME is a reserved word.  Change the field name to something else or wrap it in [ ] brackets in the SQL:

CREATE TABLE Test(id COUNTER PRIMARY KEY,[Name] TEXT (25),LastUpdated TEXT (16))
 

Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
8/18/2008 7:46:05 PM
Thanks Mike. That was it. Is there a link of a list of Reserve words? Also what if i wanted one of those to allow no duplicates? Maybe also you have a link of different things for Create Table?
0
tvb2727
8/18/2008 7:58:42 PM

A list of reserved words? DId you tried to search this, I found a list in 2 seconds!!!

http://support.microsoft.com/kb/286335

And if you want to learn more about creating Tables just look for Access Data Defintion Language, an example:

http://office.microsoft.com/en-us/access/CH010410161033.aspx

Also, the online database management I mentioned in an earlier reply also includes a wizard to export data, which will generate SQL code to create Tables, Constraints and Query's, you can learn from that also!

0
hans_v
8/18/2008 9:41:20 PM

Links to both the Access and Jet reserved words can be found here: http://www.mikesdotnetting.com/Article.aspx?ArticleID=76, along with details on how they cause the error message you got.

 


Regards Mike
[MVP - ASP/ASP.NET]
My site
0
Mikesdotnetting
8/18/2008 9:47:59 PM
Reply:

Similar Artilces:

Access a Access Database in VB code
I am trying to access my access database in the On-Change event.  I want to test if a record alreay exists for that employee on that date before I allow the insert. Imports System Imports System.Collections Imports System.Configuration Imports System.Data Imports System.Data.OleDb Imports System.Web.UI Imports System.Web.UI.WebControlsPartial Class EmpRelief  Inherits System.Web.UI.Page Protected Sub AccessDataSource1_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs) End SubProtected Sub DateWorkedTextBox_TextChanged(ByVal sender As Object, ByVal...

accessing linked Access table from .net
We have an access database with a linked table to MYOB through ODBC. The MYOB table appears in access with a green dot indicating it's a linked table. In Access it all works fine. When we try to get at it through .net, it brings up DB_SEC_E_AUTH_FAILED(0x80040E4D) which as far as I can see indicates authentication failed. Any idea what is required to be able to use it from.Net? You might try this stepp0, put in admin for the username and leave the password blank (unless you specified other) Source=C:\Inetpub\wwwroot\Components\Databases\Db.m...

Migrating Data from one Access Database table to a different Access Datbase / Table
A little background Company has a database that must have new records added to it weekly.  The new data is provided via an access database.  The old data is in a another access database.  What I want to do is have them upload the new access database to the ASP (not .net) server and then copy the new records over to the old data to bring it up to date.  At this point they can upload the new database. But I am having a hard time getting that info into the old database.   This is what I have so far,  but I get the following error Microsoft JET Database Engine...

accessing ACCESS database
Hi, I am trying to write a script that copies an information from ACCESS but it gives me the following error: Can't call method "Sql" on an undefined value at franowner.pl.txt line 4. Here's the script: use Win32::ODBC; $DSN="db1"; $db = new Win32::ODBC("db1"); $db->Sql("SELECT owner_first_name, owner_last_name FROM Owners WHERE ID=11"); if ($db->Error()) { print "Error: $db->Error"; } statement:\n$DBI:errstr\n"; ($ownerfname,$ownerlname) = $db->FetchRow(); print "Database results: first ...

xls convert sheet into Access database table and also convert access table into xls sheet in c#
hello sir how  xls convert sheet into Access database table and also convert access table into xls sheet in c# Hi, If you're working in Microsoft Excel and Microsoft Access, you can use several Access features to maintain your Excel data with the AccessLinks add-in program. For more details, see:http://office.microsoft.com/en-us/help/HA010346371033.aspx (For Excel2002) http://office.microsoft.com/en-us/excel/HP052008521033.aspx?pid=CH010003461033  (For Excel2003 and above.) Thanks.Michael Jin.Microsoft Online Community SupportPlease remember to mark the replies as answe...

accessing access
------=_NextPart_000_029B_01C29063.1E2098D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hi! I am trying to access an access mdb file for the first time. I get the following error: Can't connect(dbi::ODBC:pfawtbls.mdb ), no database driver specified and DBI_DSN env var not set at jfa_data.pl line 147 for the code: my $dbh = DBI->connect( "dbi::ODBC:pfawtbls.mdb", "", "", ) or die "Unable to connect: ". $DBI::errstr. "<br>"; What am I missing? Thanks, Jer...

Access access
========== Hi, Everyone ... I'm going to lock this thread for now. If you have a question about working with Access in Web Matrix, please start a new thread in this forum. Otherwise, this thread will get to be unwieldy. Thanks! ========== Updated 6/29/03 Hello, everyone. I've been working with the new version of Web Matrix (version .6, build 812, available right now!). In particular, I've been playing with support for Microsoft Access, which is one of the great new features in the new release. I wrote up some notes on what I've found to share some of my experiences and to a...

Accessing 2 tables from Microsoft Access database in Webpage.aspx
Hi I'm using Microsoft Access and Microsoft Visual Studio (VB) to create a webpage.I have a database (dbWarehouse). Inside in have 2 tables( tblRead, tblStock). I need to use details read form tblRead and retrieve some details from tblStock. Eg. "Barcode" from tblRead --> Access tblStock using "Barcode" to retrieve "Name" and "Qty" How can i access the 2 tables inside the webpage using SQL statements in one webpage? Regards and Thanks!   Hey, If you don't need to display the bar codes, sounds more like an inner join: select Name, Qty from tblStock s inne...

Problems accessing access database
Running netware 6.1 with SP1A. When several users access the access database the second user tries to open you will receive a message that says there is a table that is exclusivly open by another user, so we can not open the document. How to make it work with databases. Is it possible that you are using an 4.90SP1 client? If yes, you should use 4.90SP1a instead -- Marcel (using XanaNews 1.16.1.6) I can resist everything but temptation >Running netware 6.1 with SP1A. Actually 6.5 I suspect. -- Andrew C Taubman Novell Support Forums Volunteer SysOp http://sup...

Accessing an Access Database on Linux
------=_NextPart_000_000E_01C1DB0F.7665F3F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit I need to access a MS Access Database residing on a Linux box and perform some queries. Any suggestions? Thanks in advance. ------=_NextPart_000_000E_01C1DB0F.7665F3F0-- ...

How to access remote Access database?
Hello everyone, What are the permissions we have to give in the Acess db which is in remote? It would be really great if you share me some ideas. Have a good one. Thanks!!!    http://databases.aspfaq.com/database/why-can-t-i-access-a-database-or-text-file-on-another-server.htmlhttp://forums.asp.net/t/1381295.aspx...

Accessing a Microsoft Access Database
Hello all, I'm relatively new to the list, so I'm sorry if I'm going over old ground here ! I would like to use PERL to view information in a Microsoft Access Database and then use this information in other programs away from the Database. Trouble is, I'm slightly confused by all the information on offer and am not sure=20 of the best way to proceed to allow PERL to gain the information in the Database and I'm also unsure exactly what I need to install on the machine. Does anybody know of a useful web site that I can look at or does anybody have any simpl...

Accessing a MS Access database
Hi, I would like to do a lookup into an Access database, and display the result in a grid. I'm an embedded programmer being thrown into the world of .net, asp, VB, and databases so I don't know where to start :-) ...... can anyone help me ? Thanks. Really jus ta matter of making a connection, doing your query, and displaying results. Here is a sample from a module I use. The data is bound to a datagrid. Sub LoadSchedule() Dim strSrc As String = CType(Settings("src"), String) If Trim(strSrc) <> "" Then ...

Problem in accessing access database
Hi  I am establishing connection with MS access. MS Access Database is placed in the same directory as the project.Connection string is " "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=testdb.mdb"   But I am getting the error  the file "testdb.mdb" not found   Can anyone please tell me what is wrong with the connection string Regards KaranRegardsKaran GuptaFor .net,Javascript and Sql server articles and interview question visithttp://guptakaran21.googlepages.com/main Try putting the DB in the APP_Data folder or changing 'testdb.mdb&#...

Web resources about - Code to build a table in an access database - asp.net.access-datasource

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

ERISA and All Claim databases
... laws, on the books in 18 states, requiring self-insured employers to report data about the prices they pay to “all payer claims databases.” ...

Sanders’ campaign looses access to N.H. Democratic voter database - The Boston Globe Sanders’ campaign ...
... the presidential campaign of US Senator Bernie Sanders has been denied access to the New Hampshire Democratic Party’s valuable voter database ...


Under pressure, DNC restores Bernie Sanders campaign's access to their database - Videos - CBS News
A strange twist in the race for the democratic presidential nomination: the campaign of Vermont Senator Bernie Sanders has filed a complaint ...

Over 680TB of data exposed in MongoDB databases
There are at least 35,000 publicly accessible and insecure MongoDB databases on the Internet, and their number appears to be growing. Combined ...

Database error publishes data of 3 million Hello Kitty fans
Online researcher Chris Vickery uncovered a database this weekend containing the personal information of 3.3 million accounts associated with ...

MacKeeper Leak Highlights Danger of Misconfigured Databases
The Shodan port-scanning service finds at least 35,000 MongoDB databases accessible without a password.

OracleVoice: Oracle Challenges SAP On In-Memory Database Claims
Companies that run SAP’s applications should know that most of those applications can perform much better on Oracle Database than on any other ...

Resources last updated: 12/24/2015 7:37:54 AM