Temp instance of access for temp tables? 1

[jhowley58]

HI,

I create several temporary tables for use in mailmerge from access. Trouble is: they cause the database to grow at a ridiculous rate(and I have to subsequently compact it etc). From my current access system I want to create a temporary instance of access in which I can create the temporary tables. Hopefully then my database size will stabilize.

Is this a valid thing to do?. If so How?

Thanks,

JohnH.

 

[cmmrfrds]

Would an ADO disconnected recordset work instead of using temporary tables or is the table(s) needed for the recordsource on a Form or Report.

 

[jhowley58]

Wow! that was a quick reply!! Thanks.

It's a complicated thing ). I use some quite complex queries to build the temporary tables, which I use in turn to fill a permanent table used as a datasource for various mailmerge templates. I've got away with using queries for most stuff. This one I can't seem to get away from.

So I am really interested in a technique using a temporary database..

Chers JohnH

 

[votegop]

I do exactly what you are describing, and it works good for me. I export my temp tables to a temp database. That database is has no access security (avoiding password-entry issues). I open Word from code and execute a MailMerge macro. The datasource for the merge is the temp table in the temp database. The temp database has a hidden form that has the "on timer" event set to close the database after about 4 seconds. This eliminates the 2nd instance of Access after the merge is complete.

Hope this helps.

Jay

 

[jhowley58]

Thanks for your reply votegop,

That isn't really what I am wanting to do. I'm probably not explaining this very well.

I dont want to export tables to an instance of access. I want to create a new instance of access called,say, msAccTemp;Create tables in that new instance of access; Manipulate those tables along with other local tables and queries to fill a (linked)table of data which resides on a server. I then want to destroy msAccTemp.

I'll just have to blunder about until I either get what I want or find that it is not possible. I will post my results.

Thanks for your time,

John Howley

 

[jhowley58]

Hi,

When the line:

CurrentDb.Execute ("Select tblcontact.* into TempTable IN c:\joncom\temp.mdb from tblcontact&quot

is executed, I get the failure message:

[3067 Query input must contain at least one table or query.]

I know that the temp.mdb exists; I also know that the table tblcontact exists.

If the line :

CurrentDb.Execute ("Select tblcontact.* into TempTable IN temp.mdb from tblcontact&quot

is executed with temp.mdb in the current directory then the table is created OK.

I want to be able to specify the full path in the IN clause. Got any ideas?

Thanks,

John Howley

 

[cmmrfrds]

Here are a couple of examples in this thread using ADO.
Thread705-691778

 

[cmmrfrds]

A couple of thoughts on this

CurrentDb.Execute ("Select tblcontact.* into TempTable IN c:\joncom\temp.mdb from tblcontact&quot

single quotes
CurrentDb.Execute ("Select tblcontact.* into TempTable IN 'c:\joncom\temp.mdb' from tblcontact&quot

OR
Brackets
CurrentDb.Execute ("Select tblcontact.* into TempTable IN [c:\joncom\temp.mdb] from tblcontact&quot

 

[jhowley58]

Thanks cmmrfrds,

I would never have believed it!! I used:

CurrentDb.Execute ("Select tblcontact.* into TempTable IN ""c:\joncom\temp.mdb"" from tblcontact&quot

as you implied and it worked! It doesn't make a lot of sense but the damn thing worked!!

Thanks mate, take a star

John Howley

 

[cmmrfrds]

I believe it has to do with the \ which is a special character. Usually by surrounding the whole literal with quotes it will ignore the special characters. Another way is with brackets - some cases.

This may work also.
c:[\]joncom[\]temp.mdb ???
[c:\joncom\temp.mdb] ???

 

[JeremyNYC]

It looks like you got a good solution, and that's excellent. But yes, building a temp database to avoid bloat in your main back end is legit, and indeed quite standard.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.