I need to upgrade a MS Access database (Version 2002) with a script
only, i.e., via SQL statement, not via Access GUI, and I'm having
trouble defining an AutoNumber field. I got an exception error when
running the follwoing (in a C++ program using ADO):
......
cmdStr = "CREATE TABLE mytab "
"([Id] AutoNumber, "
"[Desc] TEXT(50), "
"CONSTRAINT [PK_MYTAB] PRIMARY KEY ([Id])); ";
pConn->Execute(cmdStr , NULL, adExecuteNoReco rds);
When I changed the "Id" column data type to "integer", the program
updated the Access database successfully. However, the "id" field now
becomes a regular integer field. How do I make it an AutoNumber field
with a SQL statement?
Thanks. 9 60102 mi****@excite.c om wrote: I need to upgrade a MS Access database (Version 2002) with a script only, i.e., via SQL statement, not via Access GUI, and I'm having trouble defining an AutoNumber field. I got an exception error when running the follwoing (in a C++ program using ADO):
...... cmdStr = "CREATE TABLE mytab " "([Id] AutoNumber, " "[Desc] TEXT(50), " "CONSTRAINT [PK_MYTAB] PRIMARY KEY ([Id])); "; pConn->Execute(cmdStr , NULL, adExecuteNoReco rds);
When I changed the "Id" column data type to "integer", the program updated the Access database successfully. However, the "id" field now becomes a regular integer field. How do I make it an AutoNumber field with a SQL statement?
Thanks.
I thought this was going to be an easy one. SQL Server and MySQL both
have a syntax for this but Access doesn't seem to. At least I can't find
it either. In these you define the Type as Long and then an extra
parameter makes it an Identity. The Access Help doesn't mention this
parameter, so it may not exist.
I did find that you can do it with the CreateTable method of the
DAO.Database object with an Attribute parameter. I don't know if that
helps or not. There appears to be a lot more options to CreateTable than
the SQL Create Table.
--
Bri
rkc wrote: AUTOINCREMENT
Thank you guys very much! AUTOINCREMENT worked!
rkc wrote: AUTOINCREMENT
Excellent! I searched through the Create Table help and it didn't have
specific info on Data Type. The Data Type help calles it AutoNumber not
AutoIncrement. Where did you find that AutoIncrement was the correct
Type? Sometimes help needs help.
--
Bri
Bri wrote: rkc wrote:
AUTOINCREMENT
Excellent! I searched through the Create Table help and it didn't have specific info on Data Type. The Data Type help calles it AutoNumber not AutoIncrement. Where did you find that AutoIncrement was the correct Type? Sometimes help needs help.
I have no idea where I learned half of what I know. I dug that bit
of info out of a class that writes create table ddl to an html file.
A Google search now that you know should reveal several resources.
I just did a search and discovered that COUNTER also works.
TTBOMR, when MS-Access went from JET 3.5 to JET 4.0, it was also the
time when ADPs and MSDE were at the fore-front of the glitz message
from MS. Either they forgot to tell us about JET 4.0, or many of us,
including me, forgot to look.
The !!!!!new!!!!! (6 years old?) thingmes in JET 4.0 are discussed at http://support.microsoft.com/kb/q275561/
but not as extensively as one might wish.
JET 4.0 has some great !!!!!new!!!!! capabilities, but as I see it,
they are 99.44% ignored in CDMA and elsewhere.
Lyle Fairfield wrote: TTBOMR, when MS-Access went from JET 3.5 to JET 4.0, it was also the time when ADPs and MSDE were at the fore-front of the glitz message from MS. Either they forgot to tell us about JET 4.0, or many of us, including me, forgot to look. The !!!!!new!!!!! (6 years old?) thingmes in JET 4.0 are discussed at http://support.microsoft.com/kb/q275561/ but not as extensively as one might wish. JET 4.0 has some great !!!!!new!!!!! capabilities, but as I see it, they are 99.44% ignored in CDMA and elsewhere.
Since I still do most of my development in AC97, I haven't really looked
deeply into the new features of AC2K+ let alone Jet 4. This is a very
comprehensive list of the Jet 4 features and you are right, there are a
lot of great 'new' capabilities. I was particularly impressed with the
ability to create Procedures (although I suspect they are limited to
underlying querys and do not have the features that T-SQL has) and with
the ability to do bi-directional replication with SQL Server. I've added
that link to my ever growing list of Access resources. Thanks.
--
Bri
rkc wrote: Bri wrote: rkc wrote: AUTOINCREMENT
Excellent! I searched through the Create Table help and it didn't have specific info on Data Type. The Data Type help calles it AutoNumber not AutoIncrement. Where did you find that AutoIncrement was the correct Type? Sometimes help needs help.
I have no idea where I learned half of what I know. I dug that bit of info out of a class that writes create table ddl to an html file.
A Google search now that you know should reveal several resources.
I just did a search and discovered that COUNTER also works.
Searching for AUTOINCREMENT in help found it in a comparison of data
type equivalents to ANSI SQL data types and in the list of reserved
words. Everywhere else in help it is referred to as Autonumber. I guess
this lack of consistancy is why it was so hard to find. None of the help
examples showed it's use either. In the link that Lyle supplied, it is
explained and is used in an example.
Thanks for the info.
--
Bri
When Jet 4.0 came out, we were told that:
"But to maintain backward compatibility with earlier versions of Microsoft
Jet, the enhancements to SQL are available only when the database engine is
set to operate in ANSI SQL 92 mode. "
As it turns out, either that was only partly true, or else DAO has since
been extended. I was told that I couldn't use the new ALTER TABLE stuff: it
was years later that someone else pointed out that I could.
(david)
"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ j33g2000cwa.goo glegroups.com.. . TTBOMR, when MS-Access went from JET 3.5 to JET 4.0, it was also the time when ADPs and MSDE were at the fore-front of the glitz message from MS. Either they forgot to tell us about JET 4.0, or many of us, including me, forgot to look. The !!!!!new!!!!! (6 years old?) thingmes in JET 4.0 are discussed at http://support.microsoft.com/kb/q275561/ but not as extensively as one might wish. JET 4.0 has some great !!!!!new!!!!! capabilities, but as I see it, they are 99.44% ignored in CDMA and elsewhere. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jan Hendrickx |
last post by:
Hi all,
I use (something like) following code to add new records to a database,
but it doesn't work!!!
I need to know the value of an AutoNumber-field when I add the record.
Why doesn't it work and how can I make it work???
I think it should be easy, but I can't figure it out!
|
by: Mike Wiseley |
last post by:
You can create a query in the SQL view that adds a new column to a table.
For example, to add a new text field named X10, you use the following
language:
Alter Table MyTargetTable1
Add Column X10 text;
Question: How can I make the new Column X10 be an AutoNumber field. The
following does NOT work:
|
by: ben.werdmuller |
last post by:
Hi,
Is there an easy way in ASP/VBscript to grab an autonumber (primary
key) field just after an SQL insert? This is probably easy, but I'm
stuck ..
Cheers.
|
by: S. van Beek |
last post by:
Dear reader,
By append a new record to a table I always expect the next higher value in
the range of the autonumber field.
But sometimes if I have deleted same records from a table the behaviour of
the autonumber field is changed.
|
by: Dan Keeley |
last post by:
Hi.
I'm inserting a record into a DB, how do i get the value back of an
AutoNumber field so i can use it in subsequent SQL statements?
At the moment I have code vaguely like this:
Dim selectCMD As OleDbCommand = New OleDbCommand("INSERT into AuditItems (
ParentAuditID, section, score, comments) values('1')", conn)
| |
by: HS1 |
last post by:
Hello
I have a table in Access Database. This table has a AutoNumber field. I use
a DataGrid to show that table
When I insert a new record in for this table using a DataGrid, there is a
message that asks me to enter the value for this AutoNumber field. Why I
have to do that?
Could you please help
|
by: gtwannabe |
last post by:
I'm having a problem with a form that uses AutoNumber as the primary
key. I have an Abort button to delete the current record and close the
form. If AutoNumber is assigned, the code executes a SQL statement
that deletes the current record. I need to be able to detect when
AutoNumber is unassigned (a new blank record) so that I can simply
close the form without running the SQL delete statement.
Unfortunately, no tests I can think of...
|
by: Richard Sherratt |
last post by:
I've inherited a system that was designed by someone with no
understanding of database design. From the logical design point of
view, there is no logical design. The physical design is a nightmare.
e.g. there are no relationships between tables and foreign keys appear
to have been sprinkled at random like confetti. As an example, the
Employer - Employee - Contract - Expense hierarchy has EmployerID in
the Expense table. That's the...
|
by: Amy Badgett |
last post by:
I have figured out that there is no easy way to create an autonumber field in a query, but that there is a way (supposedly) to create an autonumber field in a table or rather, there is already an autonumber field (the ID field). Is there any way I can make this ID or autonumber field to always begin with 1 so that it acts as a sort of counter/sequential numbering field for the records I put into the table?
I am appending the records from a...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |