Deleting Back-End Table 1

[LarryDeLaruelle]

Situation:

Need to delete and make a report setup table in the back end of a split database from the front end.

From what I have gathered this requires the use of workspaces but I'm not clear on how to do this.

Would appreciate any assistance you can provide.

Thanks.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[oharab]

I would use DAO and connect to the backend database and run an SQL query. Make sure you set a reference to DAO in your front end:

Dim dbBack as DAO.Database
Set dbBack=OpenDatabase("W:\Data\Database_be.mdb&quot
dbBack.Execute "DROP TABLE tblData"
dbBack.Close

To add a table use a Create Table SQL statement:
CREATE TABLE ThisTable (FirstName CHAR, LastName CHAR);

Check the help for more info, however, I really have to ask why. If you are creating a temporary table for the current user to run a report, then you should be creating that in the front end, rather than the back end, assuming you need to create the table at all!

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[LarryDeLaruelle]

Ben:

The table is used as the source for a number of cross tab queries. The data are filtered by a date range set on a form. Since cross tab queries don't like form references for criteria I decided to use the expedient of creating a temporary table to hold the filtered data. (Setting Column headings in the query's properties would not be practical since those descriptors are not static.)

The reason I wanted to create the table in the back end is because it needs to be accessable to two users -- a data clerk who does the initial creation and the first subset of reports; I then use it to create the second set of reports.

I think I have found a reasonable workaround to a Make Table query by using a delete query to clear out the existing records and then an Append (rather than a Make) query to add the new records.

As long as I run them in the proper sequence I should be OK.

Thanks for the response.

By the way, I don't have any idea where all the dumb people came form, but a lot of them have ended up where I live.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[oharab]

>.By the way, I don't have any idea where all the dumb people came form, but a lot of them have ended up where I live.
I know the feeling, strange how far idiocy has spread!

You Can use parameters with crosstab queries, but you need to specify them. Open your query in design view, on the Query menu choose parameters and enter the full field reference, eg [forms]![frmThingy].[txtCriteria] and set the data type. Your query should qork fine then.

Cheers
Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[LarryDeLaruelle]

Ben:

Many thanks. That worked very nicely.

Would be nice if MS had provided a Build feature for this but I can live with typing it in myself.

Thanks again.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org