"If ......Exists'.... Then Run SQL code"

[Guest]

HI there thanks for the help previously but heres an add on.

Ho do I structure/write:


For i=1 to 3
If i exists then Drop i and create i

i = tables

I have create table , and pretty sure i can figure out the drop table but
how do I put it all together.

Thanks for your assistance
regards

Roger

 

[Allen Browne]

If you need to dynamically create a table that has a different structure,
that will need some conditional code, but if you are just trying to get rid
of any record there, how about:

dbEngine(0)(0).Execute "DELETE FROM MyTable;", dbFailOnError

 

[Guest]

Thanks for that BUT i dont want to get rid of any records but drop the whole
table(s).
The problem with deleteing records is that the PK is autoincrementing and i
need the first record to start from 1, not necessarily from the very last
record +1 although the very last record no longer exist. Access has a
tendency to remember the last number issued. If theres a better way I am
open to suggestion!

Thanks

Roger

 

[Allen Browne]

If the AutoNumber is the problem, and this is Access 2000 or later, you
could use ADOX to set the Seed of the column that has the AutoIncrement
property.

 

[Marshall Barton]

Thanks for that BUT i dont want to get rid of any records but drop the whole
table(s).
The problem with deleteing records is that the PK is autoincrementing and i
need the first record to start from 1, not necessarily from the very last
record +1 although the very last record no longer exist. Access has a
tendency to remember the last number issued. If theres a better way I am
open to suggestion!

If you "need" the autonumber to be anything other than a
random number, then you should not be using autonumber.

If you are adding and deleting tables, your design has some
very serious mistakes. I suspect that you have gotten a
fair way down a flawed design path and are now running into
the first of the many problems caused by that design.

I suggest that you back up and look at the bigger picture to
come up with a relational design.

 

[Guest]

No doubt you are 100% correct. Do to my level of knowledge (nil) in
programming (writing scripts), I have ended up in this format. The biggest
hurdle was trying to group records in batches of 25 grouped by dates AND
list in a reporr same order as entered. Very dificult without knowing any
programming.
Always want to improve anything I do , any other suggestions would be
appreciated.

Thanks

Roger

 

[Allen Browne]

Roger, here is the code to delete all records from a table, and reset the
Seed of the AutoNumber field to 1.

To use it:

1. Click on the Modules tab of the Database window.

2. Click New. Access opens a code window.

3. From the Tools menu, choose References.

4. Check the box beside:
Microsoft ADO Ext. 2.x for DLL and Security

5. Paste in the code below.

6. Check that it compiles: Compile on Debug menu.

7. Save the module. A name like Module1 is okay.

To use the code to delete all records from a table named "MyTempTable", use
a macro with the RunCode action, and in the lower pane of macro design,
enter:
DeleteAllAndResetAutoNum("MyTempTable")

-------------code starts----------------
Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
'Return: True if sucessful.
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strSql As String

'Delete all records.
strSql = "DELETE FROM [" & strTable & "];"
CurrentProject.Connection.Execute strSql

'Find and reset the AutoNum field.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
col.Properties("Seed") = 1
DeleteAllAndResetAutoNum = True
End If
Next
End Function
-------code ends-------------

 

[Guest]

Allen,
Thankyou for your help so far, both educational and helpfl.
One more question before we close this off :
There are several different scripting languages available in Access; ADO,
DAO, VB, and sql. Now with the ecception of sql, which is the Best one to
start learning, or to concentrate on; or are they all used for different
reasons.

Thanks from a greatful

Roger

Roger, here is the code to delete all records from a table, and reset the
Seed of the AutoNumber field to 1.

To use it:

1. Click on the Modules tab of the Database window.

2. Click New. Access opens a code window.

3. From the Tools menu, choose References.

4. Check the box beside:
Microsoft ADO Ext. 2.x for DLL and Security

5. Paste in the code below.

6. Check that it compiles: Compile on Debug menu.

7. Save the module. A name like Module1 is okay.

To use the code to delete all records from a table named "MyTempTable", use
a macro with the RunCode action, and in the lower pane of macro design,
enter:
DeleteAllAndResetAutoNum("MyTempTable")

-------------code starts----------------
Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
'Return: True if sucessful.
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strSql As String

'Delete all records.
strSql = "DELETE FROM [" & strTable & "];"
CurrentProject.Connection.Execute strSql

'Find and reset the AutoNum field.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
col.Properties("Seed") = 1
DeleteAllAndResetAutoNum = True
End If
Next
End Function
-------code ends-------------

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

No doubt you are 100% correct. Do to my level of knowledge (nil) in
programming (writing scripts), I have ended up in this format. The
biggest
hurdle was trying to group records in batches of 25 grouped by dates AND
list in a reporr same order as entered. Very dificult without knowing any
programming.
Always want to improve anything I do , any other suggestions would be
appreciated.

Thanks

Roger

 

[Allen Browne]

VBA (Visual Basic for Applications) is the programming language in Access.
It provides all the basics you need, such as loops, branches, types of
variable, and so on. It also includes the Access library that allows you to
use references such as "Forms.Form1.Textbox1".

From a code window, choose References on the Tools menu, and you will see
there a dozens of availiable libraries you can add to the basic VBA/Access.
If you are working with Access tables and interface, the DAO library is the
best one to use. It is the native Access library, the one Access itself uses
behind the scenes. It is selected by default in Access versions 1, 2, 95,
97, and 2003, and lets you get to additional things like TableDefs,
QueryDefs, Recordsets, executing transactions, and a host of other things.
There is no question that it is the most suitable and efficient additional
library in all versions if you use the Access tables and interface.

In Access 2000, Microsoft tried introducing ADO instead of DAO. ADO is not
designed specifically for Access/JET, so they argued it would be better for
data stored in other sources such as SQL Server. It is less efficient than
DAO for Access stuff, and less complete. So they offered ADOX (extension to
ADO) to expose some of the missing stuff in ADO. ADOX is still incomplete
(e.g. you cannot set the DisplayControl of a field with ADOX), and is also
so buggy as to be unusable, so you still must use DAO. I think they've
realized that, and so DAO is back, referenced by default in Access 2003.

However, in their attempt to foist ADO onto us, they did not bother to
update DAO with most of the new stuff introduced in JET 4. As a result, you
*do* have to learn ADO and ADOX to be able to do some things such as running
some DDL queries (which don't even work in the Access interface since that
uses DAO itself), or resetting the Seed of an AutoNumber.

Unfortunately, the inconsistency between the libraries for various versions
means that really basic code such as:
Dim rs As Recordset
can fail or give wrong results. You also have to learn about these
inconsistences, and how to avoid them. See:
http://members.iinet.net.au/~allenbrowne/ser-38.html

So:
1. Learn VBA if you want to learn about programming.

2. Choose DAO as your extra library if you are working with Access tables
and interface.

3. After you have mastered those, you will also need to learn ADO/ADOX as
well if you want to be able to do everything.

 

[onedaywhen]

Allen Browne wrote ...

ADOX ... so buggy as to be unusable

I certainly use ADOX. I've noticed a couple of inconsistencies but
nothing major. Please give some examples of these show-stopping bugs
you've encountered.

you will also need to learn ADO/ADOX

I thought you said ADOX was unusable so why would you recommend
learning it?

Jamie.

--

 

[Allen Browne]

Here's a simple example, that just tries to set the Required property of a
column, but fails:

Sub ModifyFieldPropAdox()
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column
Dim prp As ADOX.Property

cat.ActiveConnection = CurrentProject.Connection
Set col = cat.Tables("MyTable").Columns("MyField")
Set prp = col.Properties("Nullable")
'Read the property
Debug.Print prp.Name, prp.Value, (prp.Type = adBoolean)
'Change the property
prp.Value = Not prp.Value
End Sub

There are numerous problems like that, and they are inconsistent between
versions, so if you happen to be running ADOX 2.8 and working for someone
who is running ADOX 2.7, you not only have reference problems, but the code
you write and test may not work for them.

The reason you still have to learn it is that it's the only way to get at
some of the JET 4 features, such as setting the Seed of an Autonumber, or
creating relations with cascade-to-null.

 

[Jamie Collins]

Here's a simple example, that just tries to set the Required property of a
column, but fails

I was looking for a list rather than a single example :-(

Anyhow, I think your example has misdiagnosed the problem i.e. this is
a provider bug rather than an ADOX bug e.g. using the Jet 4.0 provider
with ADODB (or Access2003 in ANSI mode) and the following DDL:

CREATE TABLE MyTable (MyField INTEGER NOT NULL);
ALTER TABLE MyTable ALTER MyField INTEGER NULL;

also fails to change the column attribute to adColNullable. So I think
we can discount your example as being a bug with ADOX.

There are numerous problems like that

they are
inconsistent between versions

It's not that I don't believe you, it's more a case of not being able
to do anything with the information without you saying what the
problems/inconsistencies actually are.

I'll do the same... I checked my notes and found the 'inconsistencies'
I alluded to earlier. I've found the provider (i.e. both ADODB and
ADOX) for a LOGICAL (YESNO) column returns CHARACTER_MAXIMUM_LENGTH=2
but I'm not sure why. Also, I've found a LOGICAL column may be created
as nullable whereas in practice it cannot be null because the DEFAULT
is always 0. So on closer examination, neither are suspected ADOX
bugs, rather are features of the provider and/or SQL engine.

Based on your anecdotal evidence and one discounted example, and
having discounted my own examples, I still consider ADOX to be
useable. However, my mind is open so more examples please!

The reason you still have to learn it is that it's the only way to
get at some of the JET 4 features, such as setting the Seed
of an Autonumber, or creating relations with cascade-to-null

Both the examples you mentioned have DDL equivalents, meaning they can
be used with ADODB or in the Access2003 UI when set to ANSI mode i.e.
ADOX is not a requirement:

IDENTITY(<seed>)
ON UPDATE SET NULL ON DELETE SET NULL

Now if you'd have said random or non-integer automnumbers I may have
been in danger of agreeing with you <g>. FWIW the only occasions I use
ADOX are to create a .mdb file and to test whether a column is has the
IDENTITY (automnumber) property. However, I find, say, random
automnumber columns of type CURRENCY a novelty rather than useful
functionality.

What most interests me is that you have found a practical use for
UPDATE/DELETE TO NULL. As usual I plea: don't just tease us with a
passing mention, share the details.

Many thanks,
Jamie.

--

 

[Guest]

Thankyou both for the useful information, MUCH appreciated