autonumber reset again

G

Guest

Hi all,

I have read thet post about resetting the autonumber, but i got an error
message that the table is in use by an other person or proces. I have to
import data from excel and add automaticly an id to it for showing which rows
contains an error, when i proces the records in an other module.

Here is my code...

Private Sub cmdInlezenMedViewV2_Click()
Dim stPlaatsBestand, stNaamBestand As String
Dim stDocName As String
Dim dbs As Database
Dim rstComplicatie As Recordset

Set dbs = CurrentDb()
Set rstComplicatie = dbs.OpenRecordset("complicaties_v2", dbOpenDynaset)
Set rstInstelling = dbs.OpenRecordset("select * from instellingen_v2")

'get first record: name and folder of import-file
rstInstelling.MoveFirst
'make filename
stDocName = rstInstelling![PlaatsImportBestand] + "\" +
rstInstelling![NaamImportBestand]

'delete old records

CurrentDb.Execute "DELETE FROM complicaties_v2", dbFailOnError

'Import data from complicaties_v2
DoCmd.TransferSpreadsheet acImport, , "complicaties_v2", stDocName, True

End Sub

Just before CurrentDb.Execute "DELETE FROM complicaties_v2", dbFailOnError I
added : CurrentDb.Execute "ALTER TABLE complicaties_v2 ALTER COLUMN Id
COUNTER (0, 1)", dbFailOnError

but this doesn't work.
What I'm doing wrong?

Thanks,
Fred
 
R

RoyVidar

Fred Baltus said:
Hi all,

I have read thet post about resetting the autonumber, but i got an
error message that the table is in use by an other person or proces.
I have to import data from excel and add automaticly an id to it for
showing which rows contains an error, when i proces the records in
an other module.

Here is my code...

Private Sub cmdInlezenMedViewV2_Click()
Dim stPlaatsBestand, stNaamBestand As String
Dim stDocName As String
Dim dbs As Database
Dim rstComplicatie As Recordset

Set dbs = CurrentDb()
Set rstComplicatie = dbs.OpenRecordset("complicaties_v2",
dbOpenDynaset) Set rstInstelling = dbs.OpenRecordset("select * from
instellingen_v2")

'get first record: name and folder of import-file
rstInstelling.MoveFirst
'make filename
stDocName = rstInstelling![PlaatsImportBestand] + "\" +
rstInstelling![NaamImportBestand]

'delete old records

CurrentDb.Execute "DELETE FROM complicaties_v2", dbFailOnError

'Import data from complicaties_v2
DoCmd.TransferSpreadsheet acImport, , "complicaties_v2", stDocName,
True

End Sub

Just before CurrentDb.Execute "DELETE FROM complicaties_v2",
dbFailOnError I added : CurrentDb.Execute "ALTER TABLE
complicaties_v2 ALTER COLUMN Id COUNTER (0, 1)", dbFailOnError

but this doesn't work.
What I'm doing wrong?

Thanks,
Fred
Click to expand...

Well, yes, you do open the complicaties_v2 table at the top of the
routine.

If you really need it open for something (which I can't find in this
routine), then either see if you can close it before you try to
execute your queries, or open it afterwards.

Since you've declared and instantiated a database object (dbs), I
think I'd use that in stead of CurrentDB when executing the queries.

dbs.Execute "DELETE FROM complicaties_v2", dbFailOnError

I would also recommend disambiguating your declarations of DAO objects,
in case you'll need other libraries having object, methods and
properties of the same name (ADO)

Dim dbs as DAO.Database
Dim rstComplicatie As DAO.Recordset
 
G

Guest

Thanks Roy,

But now i got an another error on
dbs.Execute "ALTER TABLE complicaties_v2 ALTER COLUMN Id COUNTER (0, 1)",
dbFailOnError

.....Invalid field datatype (3529) . Is the alter statement correct?

Fred

RoyVidar said:
Fred Baltus said:
Hi all,

I have read thet post about resetting the autonumber, but i got an
error message that the table is in use by an other person or proces.
I have to import data from excel and add automaticly an id to it for
showing which rows contains an error, when i proces the records in
an other module.

Here is my code...

Private Sub cmdInlezenMedViewV2_Click()
Dim stPlaatsBestand, stNaamBestand As String
Dim stDocName As String
Dim dbs As Database
Dim rstComplicatie As Recordset

Set dbs = CurrentDb()
Set rstComplicatie = dbs.OpenRecordset("complicaties_v2",
dbOpenDynaset) Set rstInstelling = dbs.OpenRecordset("select * from
instellingen_v2")

'get first record: name and folder of import-file
rstInstelling.MoveFirst
'make filename
stDocName = rstInstelling![PlaatsImportBestand] + "\" +
rstInstelling![NaamImportBestand]

'delete old records

CurrentDb.Execute "DELETE FROM complicaties_v2", dbFailOnError

'Import data from complicaties_v2
DoCmd.TransferSpreadsheet acImport, , "complicaties_v2", stDocName,
True

End Sub

Just before CurrentDb.Execute "DELETE FROM complicaties_v2",
dbFailOnError I added : CurrentDb.Execute "ALTER TABLE
complicaties_v2 ALTER COLUMN Id COUNTER (0, 1)", dbFailOnError

but this doesn't work.
What I'm doing wrong?

Thanks,
Fred
Click to expand...

Well, yes, you do open the complicaties_v2 table at the top of the
routine.

If you really need it open for something (which I can't find in this
routine), then either see if you can close it before you try to
execute your queries, or open it afterwards.

Since you've declared and instantiated a database object (dbs), I
think I'd use that in stead of CurrentDB when executing the queries.

dbs.Execute "DELETE FROM complicaties_v2", dbFailOnError

I would also recommend disambiguating your declarations of DAO objects,
in case you'll need other libraries having object, methods and
properties of the same name (ADO)

Dim dbs as DAO.Database
Dim rstComplicatie As DAO.Recordset
Click to expand...
 
R

RoyVidar

Fred Baltus said:
Thanks Roy,

But now i got an another error on
dbs.Execute "ALTER TABLE complicaties_v2 ALTER COLUMN Id COUNTER
(0, 1)", dbFailOnError

....Invalid field datatype (3529) . Is the alter statement correct?

Fred

RoyVidar said:
Fred Baltus said:
Hi all,

I have read thet post about resetting the autonumber, but i got an
error message that the table is in use by an other person or
proces. I have to import data from excel and add automaticly an id
to it for showing which rows contains an error, when i proces the
records in an other module.

Here is my code...

Private Sub cmdInlezenMedViewV2_Click()
Dim stPlaatsBestand, stNaamBestand As String
Dim stDocName As String
Dim dbs As Database
Dim rstComplicatie As Recordset

Set dbs = CurrentDb()
Set rstComplicatie = dbs.OpenRecordset("complicaties_v2",
dbOpenDynaset) Set rstInstelling = dbs.OpenRecordset("select * from
instellingen_v2")

'get first record: name and folder of import-file
rstInstelling.MoveFirst
'make filename
stDocName = rstInstelling![PlaatsImportBestand] + "\" +
rstInstelling![NaamImportBestand]

'delete old records

CurrentDb.Execute "DELETE FROM complicaties_v2", dbFailOnError

'Import data from complicaties_v2
DoCmd.TransferSpreadsheet acImport, , "complicaties_v2", stDocName,
True

End Sub

Just before CurrentDb.Execute "DELETE FROM complicaties_v2",
dbFailOnError I added : CurrentDb.Execute "ALTER TABLE
complicaties_v2 ALTER COLUMN Id COUNTER (0, 1)", dbFailOnError

but this doesn't work.
What I'm doing wrong?

Thanks,
Fred
Click to expand...

Well, yes, you do open the complicaties_v2 table at the top of the
routine.

If you really need it open for something (which I can't find in this
routine), then either see if you can close it before you try to
execute your queries, or open it afterwards.

Since you've declared and instantiated a database object (dbs), I
think I'd use that in stead of CurrentDB when executing the queries.

dbs.Execute "DELETE FROM complicaties_v2", dbFailOnError

I would also recommend disambiguating your declarations of DAO
objects, in case you'll need other libraries having object, methods
and properties of the same name (ADO)

Dim dbs as DAO.Database
Dim rstComplicatie As DAO.Recordset
Click to expand...
Click to expand...

I was thinking about the order of the statements when I answered, but
totally forgot, try switching the statements, run the delete before
the alter table.

If this doesn't work, what datatype is the field?
 
G

Guest

Roy,

I did switch the two statements, first delete, then the alter.
The field is called Id and it's an Autonumber field of type Long.
When i drop all records and compact the database the autonumber is reset to
zero.

I think that the code
ALTER TABLE complicaties_v2 ALTER COLUMN Id COUNTER (0, 1) or
ALTER TABLE complicaties_v2 ALTER COLUMN Id COUNTER (1,0) is not correct
I use an Access2000 db with DOA 3.6. Is there another way?
The delete of the rows work fine.

Thanks Fred

RoyVidar said:
Fred Baltus said:
Thanks Roy,

But now i got an another error on
dbs.Execute "ALTER TABLE complicaties_v2 ALTER COLUMN Id COUNTER
(0, 1)", dbFailOnError

....Invalid field datatype (3529) . Is the alter statement correct?

Fred

RoyVidar said:
message <[email protected]>:
Hi all,

I have read thet post about resetting the autonumber, but i got an
error message that the table is in use by an other person or
proces. I have to import data from excel and add automaticly an id
to it for showing which rows contains an error, when i proces the
records in an other module.

Here is my code...

Private Sub cmdInlezenMedViewV2_Click()
Dim stPlaatsBestand, stNaamBestand As String
Dim stDocName As String
Dim dbs As Database
Dim rstComplicatie As Recordset

Set dbs = CurrentDb()
Set rstComplicatie = dbs.OpenRecordset("complicaties_v2",
dbOpenDynaset) Set rstInstelling = dbs.OpenRecordset("select * from
instellingen_v2")

'get first record: name and folder of import-file
rstInstelling.MoveFirst
'make filename
stDocName = rstInstelling![PlaatsImportBestand] + "\" +
rstInstelling![NaamImportBestand]

'delete old records

CurrentDb.Execute "DELETE FROM complicaties_v2", dbFailOnError

'Import data from complicaties_v2
DoCmd.TransferSpreadsheet acImport, , "complicaties_v2", stDocName,
True

End Sub

Just before CurrentDb.Execute "DELETE FROM complicaties_v2",
dbFailOnError I added : CurrentDb.Execute "ALTER TABLE
complicaties_v2 ALTER COLUMN Id COUNTER (0, 1)", dbFailOnError

but this doesn't work.
What I'm doing wrong?

Thanks,
Fred

Well, yes, you do open the complicaties_v2 table at the top of the
routine.

If you really need it open for something (which I can't find in this
routine), then either see if you can close it before you try to
execute your queries, or open it afterwards.

Since you've declared and instantiated a database object (dbs), I
think I'd use that in stead of CurrentDB when executing the queries.

dbs.Execute "DELETE FROM complicaties_v2", dbFailOnError

I would also recommend disambiguating your declarations of DAO
objects, in case you'll need other libraries having object, methods
and properties of the same name (ADO)

Dim dbs as DAO.Database
Dim rstComplicatie As DAO.Recordset
Click to expand...
Click to expand...

I was thinking about the order of the statements when I answered, but
totally forgot, try switching the statements, run the delete before
the alter table.

If this doesn't work, what datatype is the field?
Click to expand...
 
T

tina

excuse me for butting into this thread, guys; i've a question, Roy. i ran
the ALTER TABLE query in an A2000 db in A2003 with no problem (just a direct
change to a local table, no recordsets, etc, involved). when i tried the
same query in an A97 db, the query failed with an error

"Syntax error in ALTER TABLE statement"

and the *second* ALTER high-lighted.

is this solution not available in A97? and if not, do you know of an
alternative that is?

tia,
tina


RoyVidar said:
Fred Baltus said:
Thanks Roy,

But now i got an another error on
dbs.Execute "ALTER TABLE complicaties_v2 ALTER COLUMN Id COUNTER
(0, 1)", dbFailOnError

....Invalid field datatype (3529) . Is the alter statement correct?

Fred

RoyVidar said:
message <[email protected]>:
Hi all,

I have read thet post about resetting the autonumber, but i got an
error message that the table is in use by an other person or
proces. I have to import data from excel and add automaticly an id
to it for showing which rows contains an error, when i proces the
records in an other module.

Here is my code...

Private Sub cmdInlezenMedViewV2_Click()
Dim stPlaatsBestand, stNaamBestand As String
Dim stDocName As String
Dim dbs As Database
Dim rstComplicatie As Recordset

Set dbs = CurrentDb()
Set rstComplicatie = dbs.OpenRecordset("complicaties_v2",
dbOpenDynaset) Set rstInstelling = dbs.OpenRecordset("select * from
instellingen_v2")

'get first record: name and folder of import-file
rstInstelling.MoveFirst
'make filename
stDocName = rstInstelling![PlaatsImportBestand] + "\" +
rstInstelling![NaamImportBestand]

'delete old records

CurrentDb.Execute "DELETE FROM complicaties_v2", dbFailOnError

'Import data from complicaties_v2
DoCmd.TransferSpreadsheet acImport, , "complicaties_v2", stDocName,
True

End Sub

Just before CurrentDb.Execute "DELETE FROM complicaties_v2",
dbFailOnError I added : CurrentDb.Execute "ALTER TABLE
complicaties_v2 ALTER COLUMN Id COUNTER (0, 1)", dbFailOnError

but this doesn't work.
What I'm doing wrong?

Thanks,
Fred

Well, yes, you do open the complicaties_v2 table at the top of the
routine.

If you really need it open for something (which I can't find in this
routine), then either see if you can close it before you try to
execute your queries, or open it afterwards.

Since you've declared and instantiated a database object (dbs), I
think I'd use that in stead of CurrentDB when executing the queries.

dbs.Execute "DELETE FROM complicaties_v2", dbFailOnError

I would also recommend disambiguating your declarations of DAO
objects, in case you'll need other libraries having object, methods
and properties of the same name (ADO)

Dim dbs as DAO.Database
Dim rstComplicatie As DAO.Recordset
Click to expand...
Click to expand...

I was thinking about the order of the statements when I answered, but
totally forgot, try switching the statements, run the delete before
the alter table.

If this doesn't work, what datatype is the field?
Click to expand...
 
R

RoyVidar

tina said:
excuse me for butting into this thread, guys; i've a question, Roy. i
ran the ALTER TABLE query in an A2000 db in A2003 with no problem
(just a direct change to a local table, no recordsets, etc,
involved). when i tried the same query in an A97 db, the query failed
with an error

"Syntax error in ALTER TABLE statement"

and the *second* ALTER high-lighted.

is this solution not available in A97? and if not, do you know of an
alternative that is?

tia,
tina


RoyVidar said:
Fred Baltus said:
Thanks Roy,

But now i got an another error on
dbs.Execute "ALTER TABLE complicaties_v2 ALTER COLUMN Id COUNTER
(0, 1)", dbFailOnError

....Invalid field datatype (3529) . Is the alter statement correct?

Fred

:

message <[email protected]>:
Hi all,

I have read thet post about resetting the autonumber, but i got
an error message that the table is in use by an other person or
proces. I have to import data from excel and add automaticly an
id to it for showing which rows contains an error, when i proces
the records in an other module.

Here is my code...

Private Sub cmdInlezenMedViewV2_Click()
Dim stPlaatsBestand, stNaamBestand As String
Dim stDocName As String
Dim dbs As Database
Dim rstComplicatie As Recordset

Set dbs = CurrentDb()
Set rstComplicatie = dbs.OpenRecordset("complicaties_v2",
dbOpenDynaset) Set rstInstelling = dbs.OpenRecordset("select *
from instellingen_v2")

'get first record: name and folder of import-file
rstInstelling.MoveFirst
'make filename
stDocName = rstInstelling![PlaatsImportBestand] + "\" +
rstInstelling![NaamImportBestand]

'delete old records

CurrentDb.Execute "DELETE FROM complicaties_v2", dbFailOnError

'Import data from complicaties_v2
DoCmd.TransferSpreadsheet acImport, , "complicaties_v2",
stDocName, True

End Sub

Just before CurrentDb.Execute "DELETE FROM complicaties_v2",
dbFailOnError I added : CurrentDb.Execute "ALTER TABLE
complicaties_v2 ALTER COLUMN Id COUNTER (0, 1)", dbFailOnError

but this doesn't work.
What I'm doing wrong?

Thanks,
Fred

Well, yes, you do open the complicaties_v2 table at the top of the
routine.

If you really need it open for something (which I can't find in
this routine), then either see if you can close it before you try
to execute your queries, or open it afterwards.

Since you've declared and instantiated a database object (dbs), I
think I'd use that in stead of CurrentDB when executing the
queries.

dbs.Execute "DELETE FROM complicaties_v2", dbFailOnError

I would also recommend disambiguating your declarations of DAO
objects, in case you'll need other libraries having object,
methods and properties of the same name (ADO)

Dim dbs as DAO.Database
Dim rstComplicatie As DAO.Recordset
Click to expand...

I was thinking about the order of the statements when I answered,
but totally forgot, try switching the statements, run the delete
before the alter table.

If this doesn't work, what datatype is the field?
Click to expand...
Click to expand...

I don't have the possibilities to test at the moment, but I *think*
the ALTER COLUMN part/syntax is available starting with Jet 4.0,
meaning, I think, not available in previous versions of the Jet engine.

Here's one KB with DAO/DDL combined approach
http://support.microsoft.com/kb/q128016/
 
R

RoyVidar

Fred Baltus said:
Roy,

I did switch the two statements, first delete, then the alter.
The field is called Id and it's an Autonumber field of type Long.
When i drop all records and compact the database the autonumber is
reset to zero.

I think that the code
ALTER TABLE complicaties_v2 ALTER COLUMN Id COUNTER (0, 1) or
ALTER TABLE complicaties_v2 ALTER COLUMN Id COUNTER (1,0) is not
correct I use an Access2000 db with DOA 3.6. Is there another way?
The delete of the rows work fine.

Thanks Fred

RoyVidar said:
Fred Baltus said:
Thanks Roy,

But now i got an another error on
dbs.Execute "ALTER TABLE complicaties_v2 ALTER COLUMN Id COUNTER
(0, 1)", dbFailOnError

....Invalid field datatype (3529) . Is the alter statement correct?

Fred

:

message <[email protected]>:
Hi all,

I have read thet post about resetting the autonumber, but i got
an error message that the table is in use by an other person or
proces. I have to import data from excel and add automaticly an
id to it for showing which rows contains an error, when i proces
the records in an other module.

Here is my code...

Private Sub cmdInlezenMedViewV2_Click()
Dim stPlaatsBestand, stNaamBestand As String
Dim stDocName As String
Dim dbs As Database
Dim rstComplicatie As Recordset

Set dbs = CurrentDb()
Set rstComplicatie = dbs.OpenRecordset("complicaties_v2",
dbOpenDynaset) Set rstInstelling = dbs.OpenRecordset("select *
from instellingen_v2")

'get first record: name and folder of import-file
rstInstelling.MoveFirst
'make filename
stDocName = rstInstelling![PlaatsImportBestand] + "\" +
rstInstelling![NaamImportBestand]

'delete old records

CurrentDb.Execute "DELETE FROM complicaties_v2", dbFailOnError

'Import data from complicaties_v2
DoCmd.TransferSpreadsheet acImport, , "complicaties_v2",
stDocName, True

End Sub

Just before CurrentDb.Execute "DELETE FROM complicaties_v2",
dbFailOnError I added : CurrentDb.Execute "ALTER TABLE
complicaties_v2 ALTER COLUMN Id COUNTER (0, 1)", dbFailOnError

but this doesn't work.
What I'm doing wrong?

Thanks,
Fred

Well, yes, you do open the complicaties_v2 table at the top of the
routine.

If you really need it open for something (which I can't find in
this routine), then either see if you can close it before you try
to execute your queries, or open it afterwards.

Since you've declared and instantiated a database object (dbs), I
think I'd use that in stead of CurrentDB when executing the
queries.

dbs.Execute "DELETE FROM complicaties_v2", dbFailOnError

I would also recommend disambiguating your declarations of DAO
objects, in case you'll need other libraries having object,
methods and properties of the same name (ADO)

Dim dbs as DAO.Database
Dim rstComplicatie As DAO.Recordset
Click to expand...

I was thinking about the order of the statements when I answered,
but totally forgot, try switching the statements, run the delete
before the alter table.

If this doesn't work, what datatype is the field?
Click to expand...
Click to expand...

I'm sorry, I really don't know. You could try executing it through
ADO, though I don' think that would or should make any difference.

This would need a reference to Microsoft ActiveX Date Objects 2.#
Library.

dim cn as adodb.connection
set cn = currentproject.connection

cn.execute "DELETE FROM complicaties_v2", , _
adcmdtext + adexecutenorecords
cn.execute "ALTER TABLE complicaties_v2 " & _
"ALTER COLUMN Id Int Identity (1, 1)", , _
adcmdtext + adexecutenorecords

One alternative, though I don't like it much, is to DROP and recreate
the table.

Or lets hope someone with experience in this pops in.
 
G

Guest

Roy,

Thanks.

Fred

RoyVidar said:
I'm sorry, I really don't know. You could try executing it through
ADO, though I don' think that would or should make any difference.

This would need a reference to Microsoft ActiveX Date Objects 2.#
Library.

dim cn as adodb.connection
set cn = currentproject.connection

cn.execute "DELETE FROM complicaties_v2", , _
adcmdtext + adexecutenorecords
cn.execute "ALTER TABLE complicaties_v2 " & _
"ALTER COLUMN Id Int Identity (1, 1)", , _
adcmdtext + adexecutenorecords

One alternative, though I don't like it much, is to DROP and recreate
the table.

Or lets hope someone with experience in this pops in.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Slow Code/Query Execution 9
Loop ending in wrong place 3
Complicated append 8
AUTONUMBER reset 7
Docmd.Setwarning False Not Working 6
HELP! - Import Loop 3
Problem with Deletes in code 5
delete sub and stay on same parent record 4

Top