SQL Design Method

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there, i have an application that im currently writting which has the option to work eaither on MSSQL or Access. I am how ever having a couple of problems with SQL code that runs fine on MSSQL but not as well on Access. Wondered if someone could help me out. The SQL Statement below refuses to work on Access, i dunno what i need to change though to allow it to work.

CREATE TABLE TempKit112122432004102044XPMachine([ItemID] [numeric](18,0) IDENTITY(1,1) NOT NULL,[DrugID] [numeric](18,0) NULL,[DrugBatchNumber] [char] (50) NULL,[DrugExpiryDate] [char] (50) NULL,[Used] [bit] NOT NULL,[Deleted] [bit] NOT NULL,[Updated] [char] (50) NULL) ON [PRIMARY]

Can anyone help or point me in the right direction to get me started?

Regards

JP
 
Hi there, i have an application that im currently writting which has
the option to work eaither on MSSQL or Access.
Click to expand...

<General rant: why does nobody on these groups ever take the time to format
their SQL quotations.... grrrr>
CREATE TABLE TempKit112122432004102044XPMachine
( [ItemID] [numeric](18,0) IDENTITY(1,1) NOT NULL,
[DrugID] [numeric](18,0) NULL,
[DrugBatchNumber] [char] (50) NULL,
[DrugExpiryDate] [char] (50) NULL,
[Used] [bit] NOT NULL,
[Deleted] [bit] NOT NULL,
[Updated] [char] (50) NULL
) ON [PRIMARY]
Click to expand...


All the [square brackets] may well be legal, but are not necessary in
either context.

As a matter of good form, I would change the NUMERIC type to a specific
one, either FLOAT, DECIMAL or REAL. Actually, for an identity column you
probably should be using INTEGER or BIGINT. The CHAR type is not in my help
file -- you should again be specific and ask for NCHAR, NVARCHAR or NTEXT
as required.

Given that Jet does not have primary or secondary volumes, the ON PRIMARY
clause is at best redundant and possibly illegal.

Another point to remember is that some clauses, like IDENTITY(1,1), are not
available to Jet-SQL via DAO, so you have to use ADO for these.

I also note that you have not specified a PRIMARY KEY constraint, nor any
FOREIGN KEY constraints, although I suspect that DrugID is a FK.

Apart from that, the overall structure should be fine with Jet or SQLS. You
might get more help from m.p.a.adp.sqlserver where the real SQL gurus hang
out.

Hope that helps



Tim F
 
Hi,

Take a look at the following articles for ideas on how to use SQL DDL in
Access:

291539 Create and Drop Tables and Relationships Using SQL DDL
http://support.microsoft.com/?id=291539

202117 ACC2000: Jet IDENTITY Datatype Seed and Increment Reset to 1
http://support.microsoft.com/?id=202117

180841 HOWTO: Common DDL SQL for the Microsoft Access Database Engine
http://support.microsoft.com/?id=180841

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights

--------------------
| Thread-Topic: SQL Design Method
| thread-index: AcQRi9FWv/pExED8QJSQW0xnH9LIwQ==
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
| From: "=?Utf-8?B?SmFtZXMgUHJvY3Rvcg==?=" <[email protected]>
| Subject: SQL Design Method
| Date: Wed, 24 Mar 2004 02:36:08 -0800
| Lines: 9
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.access.tablesdbdesign
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.tablesdbdesign:77496
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| Hi there, i have an application that im currently writting which has the
option to work eaither on MSSQL or Access. I am how ever having a couple of
problems with SQL code that runs fine on MSSQL but not as well on Access.
Wondered if someone could help me out. The SQL Statement below refuses to
work on Access, i dunno what i need to change though to allow it to work.

CREATE TABLE TempKit112122432004102044XPMachine([ItemID] [numeric](18,0)
IDENTITY(1,1) NOT NULL,[DrugID] [numeric](18,0) NULL,[DrugBatchNumber]
[char] (50) NULL,[DrugExpiryDate] [char] (50) NULL,[Used] [bit] NOT
NULL,[Deleted] [bit] NOT NULL,[Updated] [char] (50) NULL) ON [PRIMARY]

Can anyone help or point me in the right direction to get me started?

Regards

JP
|
 
All the [square brackets] may well be legal, but are not necessary in
either context.
Click to expand...

They are necessary if column names might ever contain spaces. This is
a Microsoft convention, not the SQL standard. (The SQL standard is
double quotes.)
The CHAR type is not in my help
file -- you should again be specific and ask for NCHAR, NVARCHAR or NTEXT
as required.
Click to expand...

CHAR(n) works fine in Access (Jet).
 
Back
Top