SQL this time

[Basil]

This is a different query to the one below (the memory is
the biggest issue now on that one):

Problem now is that the VBA doesn't like some SQL that
should work:

DoCmd.RunSQL "DELETE [" & TableName & "].* FROM [" &
TableName & "];"

This is what it gives:

Run-time error 3126. Invalid bracketing of name 'Booked
List'.

This has happened when TableName is 'Booked List' (If I
don't put the brackets it will not identify 'Booked' as a
table and create a different error.

When I just create the query it gives identical code to
what this text should produce and works. Any ideas?

Many thanks for all your efforts,

Basil

 

[Wayne Morgan]

Did you try

DoCmd.RunSQL "DELETE * FROM [" & TableName & "];"

If that still doesn't do it, try placing the SQL string in a string variable
them use the variable in the RunSQL command. Also, this will let you do a
Debug.Print strVariable so that you can see what is really being passed,
although is appears correct, depending on what TableName is. If TableName is
a control try Me.TableName instead.

 

[Basil]

Wayne, thanks so much for taking the time to help me. I
still haven't solved the problems but you have helped me
identify exactly what (I think) is going wrong:

SQL problem:
The TableName variable is taken from the caption of the
conrol that is clicked. Every caption is of the
form "Inpatients (rphdbip)" or "Booked List (rphdbbdl)"
etc. However the bracketed item is put on the line below
the TableName (in these cases "Inpatients" and "Booked
List". The formula I am using to extract the TableName is
as follows -

TableName = Trim(Left(Screen.ActiveControl.Caption, InStr
(1, Screen.ActiveControl.Caption, "(") - 1))

What it is doing is including the "Enter" keyboard key at
the end of the TableName. i.e. msgbox TableName & "."
would look like:

Inpatients
..

Do you know a way round this, because I am pretty sure it
is causing the sql bracketing problem.

Earlier delete problem:
It is clear that the memory problem caused the delete mess
up... I import many such tables by deleting the contents
of a stored table and forcing them in... it means the db
size goes out of control every time as if it remembers all
the deleted data and indexes. Is there any way I can get
it to not do so? compacting after every import would be
rediculous. I have noticed that deleting the whole table
and running make-table instead of append queries works so
beautifully - but it means I will not be able to hold any
indexes. Any ideas?

Your help is really appreciated.

Basil

-----Original Message-----
Did you try

DoCmd.RunSQL "DELETE * FROM [" & TableName & "];"

If that still doesn't do it, try placing the SQL string in a string variable
them use the variable in the RunSQL command. Also, this will let you do a
Debug.Print strVariable so that you can see what is really being passed,
although is appears correct, depending on what TableName is. If TableName is
a control try Me.TableName instead.

--
Wayne Morgan
Microsoft Access MVP

This is a different query to the one below (the memory is
the biggest issue now on that one):

Problem now is that the VBA doesn't like some SQL that
should work:

DoCmd.RunSQL "DELETE [" & TableName & "].* FROM [" &
TableName & "];"

This is what it gives:

Run-time error 3126. Invalid bracketing of name 'Booked
List'.

This has happened when TableName is 'Booked List' (If I
don't put the brackets it will not identify 'Booked' as a
table and create a different error.

When I just create the query it gives identical code to
what this text should produce and works. Any ideas?

Many thanks for all your efforts,

Basil

.

 

[Basil]

Just fixed the SQL problem... it seems the return key
results in more than 1 character or something - the left
(...,instr(...)-3) instead of -1 does the trick.

The db size problem I can't sort out and still need help
on.

-----Original Message-----
Wayne, thanks so much for taking the time to help me. I
still haven't solved the problems but you have helped me
identify exactly what (I think) is going wrong:

SQL problem:
The TableName variable is taken from the caption of the
conrol that is clicked. Every caption is of the
form "Inpatients (rphdbip)" or "Booked List (rphdbbdl)"
etc. However the bracketed item is put on the line below
the TableName (in these cases "Inpatients" and "Booked
List". The formula I am using to extract the TableName is
as follows -

TableName = Trim(Left(Screen.ActiveControl.Caption, InStr
(1, Screen.ActiveControl.Caption, "(") - 1))

What it is doing is including the "Enter" keyboard key at
the end of the TableName. i.e. msgbox TableName & "."
would look like:

Inpatients
..

Do you know a way round this, because I am pretty sure it
is causing the sql bracketing problem.

Earlier delete problem:
It is clear that the memory problem caused the delete mess
up... I import many such tables by deleting the contents
of a stored table and forcing them in... it means the db
size goes out of control every time as if it remembers all
the deleted data and indexes. Is there any way I can get
it to not do so? compacting after every import would be
rediculous. I have noticed that deleting the whole table
and running make-table instead of append queries works so
beautifully - but it means I will not be able to hold any
indexes. Any ideas?

Your help is really appreciated.

Basil

-----Original Message-----
Did you try

DoCmd.RunSQL "DELETE * FROM [" & TableName & "];"

If that still doesn't do it, try placing the SQL string in a string variable
them use the variable in the RunSQL command. Also, this will let you do a
Debug.Print strVariable so that you can see what is really being passed,
although is appears correct, depending on what TableName is. If TableName is
a control try Me.TableName instead.

--
Wayne Morgan
Microsoft Access MVP

This is a different query to the one below (the memory is
the biggest issue now on that one):

Problem now is that the VBA doesn't like some SQL that
should work:

DoCmd.RunSQL "DELETE [" & TableName & "].* FROM [" &
TableName & "];"

This is what it gives:

Run-time error 3126. Invalid bracketing of name 'Booked
List'.

This has happened when TableName is 'Booked List' (If I
don't put the brackets it will not identify 'Booked'

as

a


.

.

 

[John Vinson]

Just fixed the SQL problem... it seems the return key
results in more than 1 character or something - the left
(...,instr(...)-3) instead of -1 does the trick.

Correct: a return key puts in an ASCII 13 (carriage return) followed
by an ASCII 10 (linefeed).

The db size problem I can't sort out and still need help
on.

Emptying a table just leaves the deleted records on disk, but not
accessible. Subsequently running an Append or MakeTable query uses new
disk. Access simply wasn't written to efficiently use disk space; the
programmers evidently wanted to increase speed at the cost of space
(garbage collection is timeconsuming).

Is there ANY chance that you could use a Select query as the source of
any needed forms, reports, or exports? Many folks seem to assume that
the data must be stored in a table in the form in which it should be
reported in order to report it - manifestly NOT true!

 

[Basil]

Thank you very much for your response John (and the ASCII
explaination).

Regarding the use of select queries. It isn't feasible.
Select queries are used on forms from tables that are
created with the append queries. - There are 6 tables
imported, all appends of these run off each other.

I agree with your thought though, I have tried to keep the
table size to a minimum and rely on indexes in the tables
to speed up forms/reports running from select queries.

I think I will simply put a messagebox on the close button
of the update form to say, "Compact the database now!" -
unfortunately using Access 97 so no compact on close
command.

Many thanks.