Mail Merge from a Sql Temp table.

[funkytunaa]

I've had no problem in the past merging information from an Access .mdb file but since upgrading to using a SQL back end and .adp file I can't seem to mail merge a single record, I can merge ALL the records but I need to merge just the current record.

In essence what I want to do is have a button to open word and use a temp table as the data source. Word though can't seem to see the information inside the Temp Table that is created as #temp.

This is what I use for creating the temp table.

DoCmd.RunSQL "SELECT * INTO #temp FROM [letterquery] WHERE [ClientID]=" & [Forms]![Menu Letters]![clientID] & ";"

Then I open the worddocument that needs merging and run the mail merge via VB. Though it doesn't work, in theory I think it should work.

Any ideas would be appreciated.

Also one of my main problems is that I have about 270 odd fields to merge so I have even gone through the export to excel thing and that doesn't work because of the field limit.

It's driving me nuts!!!!

Cheers

 

[jrbarnett]

Temporary tables created only with one # character are invisible to other connections on the server.

There are two options:
1. Create the table with two #'s, this makes it global and such it can be accessed by other connections on the server.

2. Don't use a temporary table for this information. Insert it into a proper defined table for this.

Both the above options have the downside in that if a second person runs the program with different parameters, both users will see all records entered.
A way of getting around that would be to put the current user's username in a field in the database and pass that in as a parameter, which gets appended to a where clause.

Additionally, there's the fact that if a second person tries to create a global temp table with the same name as one that may exist, it will cause an error, so you need to check the INFORMATION_SCHEMA.TABLES view for an existing row prior to creation.

John

 

[funkytunaa]

I gave the Global temp table (##temp) a whirl and I can access the data in the temp table there and also use it for a mail merge.

I think it may have something to do with permissions but I can't narrow it down. The main reason why I thought about using the #temp source as the mail merge is because the temp table is only valid for that user which is what I want.

I wouldn't have thought it this hard to do a single record mail merge from an ADP/SQL connection.

 

[funkytunaa]

I don't suppose you figured this one out? I am currently trying to do exactly the same thing.

Cheers
Chris

 

[ozinm]

Ditto.
I suppose I could dynamically create global temp tables using the users login name but I'd rather restrict it properly.