Stumped by SQL INSERT (97)

Topic

New Reply

Trying to preserve the printer settings from a report using techniques described in Chap 10 of Access Developer’s Handbook (97). My application includes these lines of code:

sDevMode = Reports(sReportName).PrtDevMode
sSQL = “INSERT INTO atblReportSettings (ReportName, DevMode ) VALUES (‘” & _
sReportName & “‘, ‘” & sDevMode & “‘)”
CurrentDb.Execute sSQL

and sSQL resolves to:

INSERT INTO atblReportSettings (ReportName, DevMode ) 
VALUES ('rptDepartures', '???????         
???^?b??d??
???                                    ???                                           
??? ?     ^                ??              ?? 

Reply | Quote

Replies

I created a table atblReportSettings with text fields ReportName and DevMode. I then wrote code that constructs your SQL string (as copied from your post). It executes OK (it does insert a record in the table. So I don;t know what causes your problem.

Try the following: create a dummy query qryDummy. It doesn’t matter what it looks like. Then replace

CurrentDb.Execute sSQL

with

CurrentDb.QueryDefs(“qryDummy”).SQL = sSQL
CurrentDb.Execute “qryDummy”, dbFailOnError

You can view and test qryDummy after executing this code.

Reply | Quote

Most peculiar… Before trying your suggestion, I added the dbFailOnError to the .Execute statement. No difference — a message is still generated with Error #3075.

Then I used your suggestion. Same result (#3075) when trying to redefine the querydef SQL property. Then I pasted the SQL into qryDummy, and it ran successfully, both manually from the database window and from within the code after commenting-out the querydef statement.

I re-enabled the querydef statement and ran it again. Error #3075. Looking closer at the error message, it says:

Syntax error in string in query expression “???????’.

As far as my aging eyes can see, there is exactly one double-quotation, seven question marks, a single quote, and a period at the end of the message. I presume the period is applied by the message as sentence punctuation. However, that does leave a mismatched double quote and single quote.

The count of question marks matches exactly the number in the first group in the SQL statement, preceeding the first block of spaces. It’s as if some process is trying to compact the sql string by removing extra spaces, and is doing a poor job of it.

Did you run it under Access 97 SR-2? Also, this is on Windows XP Home Edition SP-2.

Stepping back to look at the big picture, maybe I am on the wrong tack for this whole process. Here’s what I am trying to accomplish:

I have developed a substantial application over the past several years in cooperation with a single client. It includes about 160 different reports, some of which are targetted for the default printer, and some of which require a special printer (colour, dot-matrix). I don’t have a dot matrix printer, and my colour printer has a different name than *his* network colour printer, so I can’t properly define the printers before I send him a new frontend.

Therefore, we developed a system whereby I will send him a new FE database with a new report or modified old report, with the report targetted to my default printer. He then opens the report in design mode, sets its properties to the correct printer, and saves the report. Then he distributes it to the staff in his office and sends the FE back to me. I import his modified copy of the report into my master copy of the FE. From then on, I never have to worry about the printer destination unless I need to modify the report design again. Works well enough for a single client.

Now we have a possibility of selling the application to a large corporation, and this method of handling the printers will not be practical. So I thought I could develop a system for the end-user to designate the target printer *once* and have that information stored in a local table. Whenever the report was modified in a new FE, there could be an process to re-establish the correct local printer without any manual intervention.

Thus I was trying to save the printer destination from the report into a table.

Is there a better method?

Thanks for your help.

Reply | Quote

I tested on Access 2002 SP-3. If I have time, I may dust off my old Windows 95/Office 97 PC at home later today.

Reply | Quote

Thanks anyway, but no need. I tried it on my office computer and it worked properly, under both 2002 SP-3 and 97 SR-2. Also did some tests with text vs memo and got variable results, so I have something to go on for looking further for the solution on my home computer.

Also — while commuting to the office I thought of a better way to approach the problem. Thanks for your input.

Reply | Quote