Deleting a table

[Maracay]

Hi Gurus

I am using this instruction to delete a table:

DBEngine(0)(0).TableDefs.Delete "tblDaoContractor"

I delete the table to created it again with different rows, that is why I
can’t just delete the records, I need to delete the table and create it
again, the problem is with this instruction if the table is not there I got
an error, if table is there everything is ok but if I run the procedure a
second time I got a run time error.

I will appreciate if someone knows how to improve this instruction.

Thanks

 

[John W. Vinson]

Hi Gurus

I am using this instruction to delete a table:

DBEngine(0)(0).TableDefs.Delete "tblDaoContractor"

I delete the table to created it again with different rows, that is why I
can’t just delete the records, I need to delete the table and create it
again, the problem is with this instruction if the table is not there I got
an error, if table is there everything is ok but if I run the procedure a
second time I got a run time error.

I will appreciate if someone knows how to improve this instruction.

Thanks

Ummmm...

Why do you feel that you can't delete the records and refill the table with an
append query? That would save the (not inconsiderable) overhead of creating a
new table and its indexes and most of the associated bloat.

Also... why do you feel that you need a new (or newly filled) table
frequently? Often you can just use a Select Query; you can base a Form, or a
Report, or an export on a select query, without any need to create a new
table.

Context please!

 

[Stefan Hoffmann]

hi,

I am using this instruction to delete a table:
DBEngine(0)(0).TableDefs.Delete "tblDaoContractor"

You may use CurrentDb as a kind of alias for DBEngine(0)(0).

I delete the table to created it again with different rows, that is why I
can’t just delete the records,

Why can't you delete the records? Does a

CurrentDb.Execute "DELETE FROM tblDaoContractor", dbFailOnError

raise any error?

I need to delete the table and create it
again, the problem is with this instruction if the table is not there I got
an error, if table is there everything is ok but if I run the procedure a
second time I got a run time error.

Use CurrentDb.TableDefs.Refresh before deleting the table.


mfG
--> stefan <--

 

[Maracay]

When I said I can’t delete the records, is not that I have problems deleting
records.
This is the situation:
I have a table with 20 columns, I need to export to Excel some of the
columns not all, the user specify, witch columns to export, they do that
filling 20 combo boxes, they may fill just 10 boxes with the columns names
to be exported, What I did was to create a new table with the fields the user
has chosen and just move the data to the new table and export it, if I try to
export the table with the 20 columns, it exports everything and I don’t want
that, everything works but the deletion of the table.

By the way the instruction CurrentDb.Execute "DELETE FROM tblDaoContractor",
dbFailOnError doesn’t delete the table.

Thanks

 

[Stefan Hoffmann]

hi,

I have a table with 20 columns, I need to export to Excel some of the
columns not all, the user specify, witch columns to export, they do that
filling 20 combo boxes, they may fill just 10 boxes with the columns names
to be exported

Take a closer look at the TableName parameter:

http://msdn.microsoft.com/en-us/library/aa220766.aspx

You can specify a query instead of a table.

By the way the instruction CurrentDb.Execute "DELETE FROM tblDaoContractor",
dbFailOnError doesn’t delete the table.

Read my post, this line is intented to delete the records, cause you
said you _cannot_ delete rows.


mfG
--> stefan <--

 

[John W. Vinson]

When I said I can’t delete the records, is not that I have problems deleting
records.
This is the situation:
I have a table with 20 columns, I need to export to Excel some of the
columns not all, the user specify, witch columns to export, they do that
filling 20 combo boxes, they may fill just 10 boxes with the columns names
to be exported, What I did was to create a new table with the fields the user
has chosen and just move the data to the new table and export it, if I try to
export the table with the 20 columns, it exports everything and I don’t want
that, everything works but the deletion of the table.

What I'd do in this circumstance is construct the SQL of a Query by building
up a WHERE clause from the fieldnames in the combo boxes, and then export
*that query*. Something like the following: give each combo box in these
twenty a Tag property of 1 to identify it:

Private Sub cmdExport_Click()
Dim strSQL As String
strSQL = "SELECT "
For Each ctl In Me.Controls
If ctl.Tag = 1 And ctl Is Not Null
strSQL = strSQL & "[" & ctl.Value & "],"
End If
Next ctl
strSQL = Left(strSQL, Len(strSQL) - 1) ' trim off trailing comma
strSQL = strSQL & " FROM tablename WHERE someappropriatecriteria;"

I'd then use the CreateQuerydef method to create a query using strSQL, and
TransferSpreadsheet to export that query to Excel.

By the way the instruction CurrentDb.Execute "DELETE FROM tblDaoContractor",
dbFailOnError doesn’t delete the table.

It durn well better not. As written it *empties* an existing table by running
a query to delete all the records in it.
CurrentDb.Tabledefs("tblDaoContractor").Delete
would delete the table.

 

[Maracay]

Hi John

The routine is working perfectly the only problem is in this line
If ctl.Tag = 1 And ctl Is Not Null, when I ask if the ctl is not null, is
giving me the error 424 Object required. I have tried different ways to do
question but nothing works

I will really appreciated if you can give me a hand with this

Thanks

When I said I can’t delete the records, is not that I have problems deleting
records.
This is the situation:
I have a table with 20 columns, I need to export to Excel some of the
columns not all, the user specify, witch columns to export, they do that
filling 20 combo boxes, they may fill just 10 boxes with the columns names
to be exported, What I did was to create a new table with the fields the user
has chosen and just move the data to the new table and export it, if I try to
export the table with the 20 columns, it exports everything and I don’t want
that, everything works but the deletion of the table.

What I'd do in this circumstance is construct the SQL of a Query by building
up a WHERE clause from the fieldnames in the combo boxes, and then export
*that query*. Something like the following: give each combo box in these
twenty a Tag property of 1 to identify it:

Private Sub cmdExport_Click()
Dim strSQL As String
strSQL = "SELECT "
For Each ctl In Me.Controls
If ctl.Tag = 1 And ctl Is Not Null
strSQL = strSQL & "[" & ctl.Value & "],"
End If
Next ctl
strSQL = Left(strSQL, Len(strSQL) - 1) ' trim off trailing comma
strSQL = strSQL & " FROM tablename WHERE someappropriatecriteria;"

I'd then use the CreateQuerydef method to create a query using strSQL, and
TransferSpreadsheet to export that query to Excel.

By the way the instruction CurrentDb.Execute "DELETE FROM tblDaoContractor",
dbFailOnError doesn’t delete the table.

It durn well better not. As written it *empties* an existing table by running
a query to delete all the records in it.
CurrentDb.Tabledefs("tblDaoContractor").Delete
would delete the table.

 

[Douglas J. Steele]

Try:

If ctl.Tag = 1 And IsNull(ctl) = False Then

(Note that the original code was missing the Then keyword)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi John

The routine is working perfectly the only problem is in this line
If ctl.Tag = 1 And ctl Is Not Null, when I ask if the ctl is not null, is
giving me the error 424 Object required. I have tried different ways to do
question but nothing works

I will really appreciated if you can give me a hand with this

Thanks

When I said I can't delete the records, is not that I have problems
deleting
records.
This is the situation:
I have a table with 20 columns, I need to export to Excel some of the
columns not all, the user specify, witch columns to export, they do that
filling 20 combo boxes, they may fill just 10 boxes with the columns
names
to be exported, What I did was to create a new table with the fields the
user
has chosen and just move the data to the new table and export it, if I
try to
export the table with the 20 columns, it exports everything and I don't
want
that, everything works but the deletion of the table.

What I'd do in this circumstance is construct the SQL of a Query by
building
up a WHERE clause from the fieldnames in the combo boxes, and then export
*that query*. Something like the following: give each combo box in these
twenty a Tag property of 1 to identify it:

Private Sub cmdExport_Click()
Dim strSQL As String
strSQL = "SELECT "
For Each ctl In Me.Controls
If ctl.Tag = 1 And ctl Is Not Null
strSQL = strSQL & "[" & ctl.Value & "],"
End If
Next ctl
strSQL = Left(strSQL, Len(strSQL) - 1) ' trim off trailing comma
strSQL = strSQL & " FROM tablename WHERE someappropriatecriteria;"

I'd then use the CreateQuerydef method to create a query using strSQL,
and
TransferSpreadsheet to export that query to Excel.

By the way the instruction CurrentDb.Execute "DELETE FROM
tblDaoContractor",
dbFailOnError doesn't delete the table.

It durn well better not. As written it *empties* an existing table by
running
a query to delete all the records in it.
CurrentDb.Tabledefs("tblDaoContractor").Delete
would delete the table.

 

[Maracay]

Thanks

Try:

If ctl.Tag = 1 And IsNull(ctl) = False Then

(Note that the original code was missing the Then keyword)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi John

The routine is working perfectly the only problem is in this line
If ctl.Tag = 1 And ctl Is Not Null, when I ask if the ctl is not null, is
giving me the error 424 Object required. I have tried different ways to do
question but nothing works

I will really appreciated if you can give me a hand with this

Thanks

On Sat, 31 Jan 2009 16:14:00 -0800, Maracay

When I said I can't delete the records, is not that I have problems
deleting
records.
This is the situation:
I have a table with 20 columns, I need to export to Excel some of the
columns not all, the user specify, witch columns to export, they do that
filling 20 combo boxes, they may fill just 10 boxes with the columns
names
to be exported, What I did was to create a new table with the fields the
user
has chosen and just move the data to the new table and export it, if I
try to
export the table with the 20 columns, it exports everything and I don't
want
that, everything works but the deletion of the table.

What I'd do in this circumstance is construct the SQL of a Query by
building
up a WHERE clause from the fieldnames in the combo boxes, and then export
*that query*. Something like the following: give each combo box in these
twenty a Tag property of 1 to identify it:

Private Sub cmdExport_Click()
Dim strSQL As String
strSQL = "SELECT "
For Each ctl In Me.Controls
If ctl.Tag = 1 And ctl Is Not Null
strSQL = strSQL & "[" & ctl.Value & "],"
End If
Next ctl
strSQL = Left(strSQL, Len(strSQL) - 1) ' trim off trailing comma
strSQL = strSQL & " FROM tablename WHERE someappropriatecriteria;"

I'd then use the CreateQuerydef method to create a query using strSQL,
and
TransferSpreadsheet to export that query to Excel.

By the way the instruction CurrentDb.Execute "DELETE FROM
tblDaoContractor",
dbFailOnError doesn't delete the table.

It durn well better not. As written it *empties* an existing table by
running
a query to delete all the records in it.
CurrentDb.Tabledefs("tblDaoContractor").Delete
would delete the table.