SLQ String Truncating in Code

[Guest]

Using a code example from Dev Ashish on the MPVS website (Simplify action
queries in code), I'm attempting (being the operative word) to load a
recordset when opening my form pulling the sql code stored in a table. The
table field (SQLString) datatype is set as "Memo", but the code truncates the
sql string (586 characters). The code is -
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from tblSQL where" _
& " CalledFromProc='StoreSchedule'", dbOpenDynaset)

With rs
strSQL = adhHandleQuotes(!SQLString)
db.Execute strSQL, dbFailOnError
End With

I'm using a DAO recordset. I've tried it with the function
"adhHandleQuotes" and without, but the error box shows the sql string
truncated both ways. Any suggestions? Thanks in advance for the help!

 

[Douglas J. Steele]

Are you saying that the SQL is supposed to be 586 bytes, and it's being
truncated to something shorted (presumably 255 bytes), or is the SQL being
truncated to 586 bytes?

What version of Access are you using? If Access 2000 or newer, try changing
the following line in adhHandleQuotes

adhHandleQuotes = QUOTE & adhReplace(strValue, QUOTE, _
QUOTE & " & Chr$(34) & " & QUOTE) & QUOTE

to

adhHandleQuotes = QUOTE & Replace(strValue, QUOTE, _
QUOTE & " & Chr$(34) & " & QUOTE) & QUOTE

and get rid of the adhReplace function completely.

 

[Guest]

That sorta worked. The error message still comes up, but when I close it,
the form opens with part of the data. The SQL query contains an equal join
between two tables and only data from the first table listed populates.

SQL Code-
SELECT s.Store, m.StreetAddress, s.Vendor, s.SchDate, s.InstallDate,
s.Pilot, s.VendorSignoff, s.CustomerSignoff,
tblStoreScheduleTicket, s.OrgSchDate, s.DateModified, s.DateAdded,
m.TimeZone, s.InstallStatus, m.TypeStore,
m.FranchiseName, m.LaneCount, s.LateCancel, s.EscalationRequired, m.Country,
m.Zip, m.State, m.City, m.Address, m.TypePart, m.PhoneNumber, m.OpenDate,
m.CloseDate, m.TZOrder, m.TZCode, s.StartOfWeek
FROM tblStoreSchedule AS s INNER JOIN qryAll_MasterStoreListInfo AS m ON
s.Store = m.Store
ORDER BY s.Store;

 

[Douglas J. Steele]

What happens when you run the query manually?

 

[Guest]

Using the SQL code as a query tied to a form works great.

My intent is to find an easier way to maintain the code. I have several
forms that users need to edit. I have a master query that I use for various
forms with parameter-variations (time zone, BusinessType). There are
currently six parameter-variations for one form and 19 parameter-variations
for another form. So I have six forms and 19 forms using hard-code parameter
changes. I've played with passing parameters to a function, but it only
worked as read-only. I thought if I could store the SQL code in a table it
would easier to update. I know this can be done, but the code-examples I've
found so far are always just different enough to not be helpful. (I do have
the Getz books for Access 2000.) Any suggestions, go-look-at links,
reference material would be GREATLY appreciated. Thanks.

 

[Douglas J. Steele]

Hold on a moment. Your original example implied you were trying to run a
series of Action queries (INSERT INTO, UPDATE or DELETE). That's all you can
use db.Execute strSQL, dbFailOnError for.