|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Microsoft Office Access has encountered a problem and needs to close Error When Linking Tables |
| Author |
Message |
|
|
| Posted : 2008-05-04 03:04:00 |
Several times (but not every time) when I try to add a new linked table in my Access 2003 database via New > Linked Table > ODBC Databases > Machine Data Source > QuickBooks Data > Terms ... I get the "Microsoft Office Access has encountered a problem and needs to close. We are sorry for the inconvenience." dialog box. The table has not been linked when I return to Access.
This happens every time I complete the process of certifying linking in QuickBooks and unpredictably after I have already successfully linked other tables. It also happens when I refresh the links in Linked Table Manager.
I have QuickBooks open, I have selected Locate Company File, and I am on v8.00.00.243.
Please help. |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-05 09:21:50 |
When using MS Access 2003, if you receive an error message that is similar to the following:-
Microsoft Office Access has encountered a problem and needs to close. We are sorry for the inconvenience. To see what data this error report contains, click here.
and when you view the data in the error report, the report contains an error signature that is similar to one of the following error signatures:-
| Application name |
Application version |
Module name |
Module version |
Offset |
| Msaccess.exe |
11.0.6355.0 |
Msaccess.exe |
11.0.6355.0 |
0001af50 |
| Msaccess.exe |
11.0.5614.0 |
Msaccess.exe |
11.0.5614.0 |
003ccf2e |
| Msaccess.exe |
11.0.5614.0 |
Msaccess.exe |
11.0.5614.0 |
00032c94 |
| Msaccess.exe |
11.0.5614.0 |
Msaccess.exe |
11.0.5614.0 |
0002cd0d |
| Msaccess.exe |
11.0.6255.0 |
Oleaut32.dll |
5.1.2600.2118 |
00008b10 |
| Msaccess.exe |
11.0.5614.0 |
Msaccess.exe |
11.0.5614.0 |
0001af37 |
| Msaccess.exe |
11.0.6355.0 |
Owc10.dll |
10.0.6712.0 |
0020eb79 |
you need to download and apply the Office 2003 Service Pack 2 found at: http://support.microsoft.com/kb/906460/ |
|
|
|
|
|
| Posted : 2008-05-05 13:10:34 |
I have been on SP2 for several years. Here is the error screen:
|
|
|
|
|
|
| Posted : 2008-05-05 14:39:40 |
It also produces the same error screen very consistently with this code when it executes rIL.Update. It worked for the first three records but now will not go beyond.
Set rDB = CurrentDb
Set rIH = rDB.OpenRecordset("SELECT * FROM tbl_InvoiceHeader WHERE Uploaded=False ORDER BY InvoiceID;", dbOpenDynaset)
Set rIL = rDB.OpenRecordset("SELECT * FROM InvoiceLine WHERE TxnID='X';", dbOpenDynaset)
iInvs = rIH.RecordCount
iICnt = 1
Do Until rIH.EOF
Set rIS = rDB.OpenRecordset("SELECT * FROM qry_InvoiceSumLine WHERE InvoiceID=" & rIH!InvoiceID & ";", dbOpenSnapshot)
rIS.MoveLast
rIS.MoveFirst
iLines = rIS.RecordCount
For iCnt = 1 To iLines
Progress iICnt, iCnt, iInvs
rIL.AddNew
rIL!RefNumber = rIS!InvoiceID
rIL!CustomerRefListID = rIS!CustomerListID
rIL!DueDate = rIS!DueDate
rIL!TxnDate = rIS!InvoiceDate
rIL!InvoiceLineItemRefListID = rIS!ItemRefListID
rIL!InvoiceLineDesc = rIS!DetailType
rIL!InvoiceLineQuantity = rIS!SumOfQty
rIL!InvoiceLineRate = rIS!Rate
rIL!InvoiceLineSalesTaxCodeRefListID = "20000-1123278384"
If iCnt < iLines Then ' Last one is False
rIL!FQSaveToCache = True
Else
rIL!FQSaveToCache = False
End If
rIL.Update
rIS.MoveNext
Next
rIH.Edit
rIH!Uploaded = True
rIH.Update
rIH.MoveNext
iICnt = iICnt + 1
Loop
However, the following code has never generated an error:
Set rDBComp = CurrentDb.OpenRecordset("SELECT * FROM tbl_Company WHERE CompanyID=" & Me.CompanyID & ";", dbOpenSnapshot)
sQBFields = "[ListID], [Name], [CompanyName], [Phone], [AltPhone], [Fax], [Email], " & _
"[ShipAddressAddr1], [ShipAddressAddr2], [ShipAddressAddr3], [ShipAddressCity], [ShipAddressState], [ShipAddressPostalCode], " & _
"[BillAddressAddr1], [BillAddressAddr2], [BillAddressAddr3], [BillAddressCity], [BillAddressState], [BillAddressPostalCode]"
If IsNull(CustomerListID) Then
Set rQBCust = CurrentDb.OpenRecordset("SELECT " & sQBFields & " FROM Customer", dbOpenDynaset)
rQBCust.AddNew
rQBCust![Name] = Left(rDBComp!Company, 41)
Else
Set rQBCust = CurrentDb.OpenRecordset("SELECT " & sQBFields & " FROM Customer WHERE ListID='" & CustomerListID & "';", dbOpenDynaset)
rQBCust.Edit
End If
rQBCust![CompanyName] = Left(rDBComp!Company, 41)
rQBCust![ShipAddressAddr1] = rDBComp!CAddress1
rQBCust![ShipAddressAddr2] = rDBComp!CAddress2
rQBCust![ShipAddressAddr3] = rDBComp!CAddress3
rQBCust![ShipAddressCity] = rDBComp!CCity
rQBCust![ShipAddressState] = rDBComp!CState
rQBCust![ShipAddressPostalCode] = rDBComp!CZip
rQBCust![BillAddressAddr1] = rDBComp!BAddress1
rQBCust![BillAddressAddr2] = rDBComp!BAddress2
rQBCust![BillAddressAddr3] = rDBComp!BAddress3
rQBCust![BillAddressCity] = rDBComp!BCity
rQBCust![BillAddressState] = rDBComp!BState
rQBCust![BillAddressPostalCode] = rDBComp!BZip
rQBCust![Phone] = rDBComp!Phone
rQBCust![AltPhone] = rDBComp![Toll-Free]
rQBCust![Fax] = rDBComp!Fax
rQBCust![Email] = rDBComp!BEmail
rQBCust.Update
If IsNull(CustomerListID) Then
sCustListID = DLookup("[ListID]", "Customer", "[Name]='" & rDBComp!Company & "'")
CustomerListID = sCustListID
cmbCustomerSel.Requery
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If
By the way, do you know a better way to get the ListID for a new record?
Let me know if you want a trace or something.
Thanks for your help.
Chris |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-05 14:50:48 |
Most MS Access programmers are using DoCmd.RunSQL commands like this:-
DoCmd.RunSQL "INSERT INTO QB_InvoiceLine ( RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, " & _
"InvoiceLineQuantity, InvoiceLineRate, InvoiceLineClassRefListID, InvoiceLineTaxCodeRefListID, " & _
"CustomFieldVFBatchNo, FQSaveToCache )" & _
"VALUES ('Me.RefNumber', 'Me.InvoiceLineItemRefListID', 'Me.InvoiceLineDesc', 'Me.InvoiceLineQuantity', " & _
"'Me.InvoiceLineRate', 'Me.InvoiceLineClassRefListID', 'Me.InvoiceLineTaxCodeRefListID', 'Me.CustomFieldVFBatchNo', 0)"
See also: When I use Linked tables in MS Access the ODBC link locks-up? |
|
|
|
|
|
| Posted : 2008-05-05 14:59:56 |
| I have used that method as well, but it causes problems with names that include aprostrophes and other special characters. I would rather fix the actual problem rather than look for a work-around right away, and I am still having the original problem. |
|
|
|
|
|
| Posted : 2008-05-06 00:02:01 |
I re-wrote the code replacing what is commented and still get the same error. BTW, I can view InvoiceLine without any problems.
' rIL.AddNew
' rIL!RefNumber = rIS!InvoiceID
' rIL!CustomerRefListID = rIS!CustomerListID
' rIL!DueDate = rIS!DueDate
' rIL!TxnDate = rIS!InvoiceDate
' rIL!InvoiceLineItemRefListID = rIS!ItemRefListID
' rIL!InvoiceLineDesc = rIS!DetailType
' rIL!InvoiceLineQuantity = rIS!SumOfQty
' rIL!InvoiceLineRate = rIS!Rate
' rIL!InvoiceLineSalesTaxCodeRefListID = "20000-1123278384"
'
' If iCnt < iLines Then ' Last one is False
' rIL!FQSaveToCache = True
' Else
' rIL!FQSaveToCache = False
' End If
' rIL.Update
DoCmd.RunSQL "" & _
"INSERT INTO InvoiceLine ( " & _
"RefNumber, " & _
"CustomerRefListID, " & _
"DueDate, " & _
"TxnDate, " & _
"InvoiceLineItemRefListID, " & _
"InvoiceLineDesc, " & _
"InvoiceLineQuantity, " & _
"InvoiceLineRate, " & _
"InvoiceLineSalesTaxCodeRefListID, " & _
"FQSaveToCache ) " & _
"VALUES ( '" & _
rIS!InvoiceID & "', '" & _
rIS!CustomerListID & "', #" & _
rIS!DueDate & "#, #" & _
rIS!InvoiceDate & "#, '" & _
rIS!ItemRefListID & "', '" & _
rIS!DetailType & "', " & _
rIS!SumOfQty & ", " & _
rIS!Rate & ", " & _
"'20000-1123278384', " & _
IIf(iCnt < iLines, "True", "False") & " );" |
|
|
|
|
|
| Posted : 2008-05-06 12:21:31 |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-06 15:26:17 |
You clearly state some things work while other things cause exception errors within MS Access. You we need to go through the trace log to see what the true nature of the errors are.
See: How do I turn on the trace log?
That doesn't mean post the whole trace file. Run the program until an error and then look at the end of the trace to see what was happening at that point of time only. |
|
|
|
|
|
| Posted : 2008-05-07 14:11:43 |
Sorry. I do not really know what I am looking at. But here is what I do know:
When I single step through that code, the exception occurs at the moment the program executes the rs.update or when it executes the insert query (I have written and tested the code both ways, as above). Before I step past those lines, I use the ControlTip feature in the compiler to verify that all the variables are delivering data to the fields in InvliceLines -- and they are.
The trace seems to refer to that Insert operation right before the end of the file, but the rest does not mean anything to me. Can you please interpret it? Here is the last section:
ISAMPrepare
0x09B49938 [ISAM]
INSERT INTO "InvoiceLine" ("CustomerRefListID","TxnDate","RefNumber","DueDate","InvoiceLineItemRefListID","InvoiceLineDesc","InvoiceLineQuantity","InvoiceLineRate","InvoiceLineSalesTaxCodeRefListID","FQSaveToCache") VALUES (?,?,?,?,?,?,?,?,?,?)
246
0x00000000 [ISAMStatement]
0
10003
ISAMCaseSensitive
0x09B49938 [ISAM]
FALSE
ISAMMaxTableNameLength
0x09B49938 [ISAM]
63
ISAMOpenTable
0x09B49938 [ISAM]
Table Name: InvoiceLine
Table Alias:
Read Only: FALSE
Query Index: 1
Unoptimized: TRUE
NoSync: FALSE
DoSync: FALSE
DoFullSync: FALSE
DoChunking: FALSE
ErrorOnMissing: TRUE
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-07 14:49:13 |
Ok, take your values and subsitute them in:
INSERT INTO "InvoiceLine" ("CustomerRefListID","TxnDate","RefNumber","DueDate","InvoiceLineItemRefListID","InvoiceLineDesc","InvoiceLineQuantity","InvoiceLineRate","InvoiceLineSalesTaxCodeRefListID","FQSaveToCache") VALUES (?,?,?,?,?,?,?,?,?,?)
using VBDemo to see what the true error is. |
|
|
|
|
|
| Posted : 2008-05-09 01:24:47 |
Here is the string I entered into VB Demo 32:
INSERT INTO InvoiceLine (CustomerRefListID,TxnDate,RefNumber,DueDate,InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES ("640000-1130425947", #6/4/2008#, "487", #6/4/2008#, "F0000-1139330365", "Spanish", 12, 1.5, "20000-1123278384", -1);
Here is the error:
[QODBC] Expected lexical element not found: )
I am pretty sure this statement does not need another paren. Can you see what I am not?
Chris |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-09 08:05:27 |
| Ok when using VBDemo you can't use the MS Access date format: #6/4/2008#, it needs to be: {d'2008-04-06'}. Also FQSaveToCache needs to be either 0 or 1, not -1. |
|
|
|
|
|
| Posted : 2008-05-10 03:13:42 |
Entered:
INSERT INTO InvoiceLine (CustomerRefListID, TxnDate, RefNumber, DueDate, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ("640000-1130425947", {d'2008/04/06'}, "487", {d'2008/04/06'}, "F0000-1139330365", "Spanish", 12, 1.5, "20000-1123278384", 1);
Got:
[QODBC] Expected lexical element not found:<identifier>
I can't seem to get this right. What do you see now?
CH |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-10 08:52:44 |
| Not {d'2008/04/06'} it should be {d'2008-04-06'} |
|
|
|
|
|
| Posted : 2008-05-11 15:52:52 |
Sorry for all the errors. I think I finally have something interesting to look at.
Here is what I entered:
INSERT INTO InvoiceLine (CustomerRefListID, TxnDate, RefNumber, DueDate, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('640000-1130425947', {d'2008-04-06'}, '487', {d'2008-04-06'}, 'F0000-1139330365', 'Spanish', 12, 1.5, '20000-1123278384', 1)
I got the following response from VB Demo, followed by an exception as shown. Do you have any insight to offer?
 |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-12 08:38:01 |
Sorry, I have no problem with the INSERT (using the right values from my company file):-
INSERT INTO InvoiceLine (CustomerRefListID, TxnDate, RefNumber, DueDate,
InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate,
InvoiceLineSalesTaxCodeRefListID, FQSaveToCache)
VALUES ('800000AB-1197771382', {d'2008-04-06'}, '487', {d'2008-04-06'},
'40000-933272655', 'Spanish', 12, 1.5,
'10000-999022286', 0)
I'm not to sure why you're using Build 243 when Build 242 is the current Public QC Tested version? I suggest you reboot, and try the inserts on another company file. It is possible you have a corrupted company file. Also, make sure you clearly state what version of QuickBooks you are using in any reply. |
|
|
|
|