Need help creation of tables

[evileye]

Hi everyone, santosh here.

I need some help regarding creation of tables for a vb.net web-based application.

DATABASE TO BE USED: MS-ACCESS 2003

Database name: Physician

I want to run this script to create new table(PC_USER).

CREATE TABLE [dbo].[PC_Users] (
[User_ID] [smallint] IDENTITY (1, 1) NOT NULL ,
[User_Name] [varchar] (25) ,
[User_Password] [varchar] (50) NULL ,
[User_Facility_ID] [int] NULL ,
[User_Admin] [char] (1) NULL ,
[Insert_dt] [datetime] NULL ,
[Update_dt] [datetime] NULL ,
[Insert_by] [varchar] (50) NULL ,
[Update_by] [varchar] (50) NULL ,
[Active] [char] (1) NULL
) ON [PRIMARY]
GO

Please let me know how can i do this? I am just a average coder in VB.net, not well knowledge in connection codes for DB. Thank you.

 

[jhomminga]

Recommend you use ADOX

MsAccess datatypes are different than SQL SERVER.

You have three options:
0) Recommended: Use the ADOX catalog and define the database and tables programatically.

Note: there is no ADOX managed provider. It is all COM but works great in VB. Here is a link to get started
http://www.codeguru.com/vb/gen/vb_database/microsoftaccess/article.php/c5149/

1) MsAccess installed and create a new MDB and then a table in the MDB.

2) use the ADODB Com library and issue DDL commands via Connection.Execute(TheDDLCommand)
Here are some examples
http://support.microsoft.com/default.aspx?scid=kb;en-us;180841
http://support.microsoft.com/kb/115833

HTH

 

[evileye]

Thanks, but i would be more happy if you could send me the code, as this is my frist attempt to CODE in vb.net WITH DB SUPPORT. I am reading the tutorials. Thanks again.

 

[cjard]

Google for "Jet DDL" - what you posted was sql server sql, not access sql..

 

[jhomminga]

Here is a complete working example:

Attached a complete tested example in zip file

:

It creates a database(mdb file) if it doesn't exists
It creates the table if it doesn't exists
It creates the columns with autoincrement data types and default values
It creates a primary key on the table

It adds a sample row to the database
It opens the database


Note: if you don't see these references in your project, download the latest mdac from http://www.microsoft.com/data and search downloads link on left for MDAC2.8