DAO.Database Error 3127
I get the error attached in the screenshot when running a series of action queries (set in motion by code) to archive the customer transactions.
I tried running the action queries individually, and the query that produces the error (in the code below) does not contain the word or the field driver. There is a field "Driver" in both normal and archive sides, but they are of the same data format, and the form used to initiate this code, does not have a driver object - so I befuddled. Any direction would be greatly appreciated.
Rick
The query:
INSERT INTO ArcCusTrxItems
SELECT CusTrxItems.*
FROM CusTrx INNER JOIN CusTrxItems ON CusTrx.TrxID = CusTrxItems.CusTrxID
WHERE (((CusTrx.TrxDate)<=pForms
ORDER BY CusTrx.TrxID, CusTrx.TrxType, CusTrxItems.ProdCode
WITH OWNERACCESS OPTION;
I tried running the action queries individually, and the query that produces the error (in the code below) does not contain the word or the field driver. There is a field "Driver" in both normal and archive sides, but they are of the same data format, and the form used to initiate this code, does not have a driver object - so I befuddled. Any direction would be greatly appreciated.
Rick
The query:
INSERT INTO ArcCusTrxItems
SELECT CusTrxItems.*
FROM CusTrx INNER JOIN CusTrxItems ON CusTrx.TrxID = CusTrxItems.CusTrxID
WHERE (((CusTrx.TrxDate)<=pForms
ORDER BY CusTrx.TrxID, CusTrx.TrxType, CusTrxItems.ProdCode
WITH OWNERACCESS OPTION;
John Tsioumpris
Are you sure that this is Microsoft Word related?
ASKER
No its not - its Microsoft Access - I missed the target in my haste - sorry. Should I remove and re-post? can it be re-labeled?
I don't know..give it a try...I think you can change the tag easily
As for the error I am pretty sure that something simply is missing
I think you need this
qdf.Connect = "ODBC;Driver={SQL Server Native Client 10.0};Server=" & NAMEOFYOURSERVER & ";Database=" & NAMEOFYOURDATABASE & "; Trusted_Connection=yes"
qdf.Connect = "ODBC;Driver={SQL Server Native Client 10.0};Server=" & NAMEOFYOURSERVER & ";Database=" & NAMEOFYOURDATABASE & "; Trusted_Connection=yes"
ASKER
Thanks John, I appreciate the input. The backend has already been established as a Trusted connection with all clients
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or the alternative is to change the field name.
Jim.
Jim.
ASKER
Now, I am curious... So, the wild card * is not an option anymore in the later versions of Access? I'll try out these suggestions and post back. Unfortunately, the host process is now taking orders for tomorrow, and I can't effectively make any changes involving the Trx or TrxItems tables until the host is done. I will keep you apprised.
Rick
Rick
<<So, the wild card * is not an option anymore in the later versions of Access?>>
It is, but some names mean things to Access (reserved words) and it will get confused unless the field name is bracketed.
That's always been true (try using a field called 'Date' sometime), and it's true in other languages/products as well.
Jim.
It is, but some names mean things to Access (reserved words) and it will get confused unless the field name is bracketed.
That's always been true (try using a field called 'Date' sometime), and it's true in other languages/products as well.
Jim.
Is this a pass-through query?
Just to be clear, it's not that you can't use SELECT *, it's only because of the field name that you have. If you did not have a field called "Driver", it would not be a problem.
Jim.
Jim.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Excellent point. Should have thought of that.
Jim.
Jim.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you use Select *, the columns are mapped by name not by positionI've had this exact situation bite me in the rear. I used SELECT * with INSERT, and couldn't figure out why my it was failing. This was on a SQL system, so I ran a trace and found that my SELECT columns weren't in the order I was expecting, and I had to list them out by hand. I don't recall how I thought they were being listed - by ordinal, position, or name - but it reminded me to never assume anything (since we all know what that leads to!).
Only time I don't list out the columns is when I use SELECT INTO (since that creates a new table).
ASKER
I will report back Tuesday when I am on site. It sounds like these comments are headed in the right direction.
ASKER
I tried selecting all of the fields as suggested, and I ran into the same problem. Now this used to work, even though the archive side of CusTrx has less than half the fields of the normal side. However, I think I have found the original cause of the issue, and why that error started appearing.
There were considerable changes made to product codes that would have diminished the integrity of the relationships. So I am thinking that as changes were made by the end user, they might have been prompted by messages like "You cannot change this record because there are related records in other tables, unless you break the relationship first. Would you like to break that relationship now?" to which they answered YES. Upon checking the relationships, I found several key relationships missing. Among them two major relations between CusTrx, CusTrxItems, and Customers. There were episodes where Customers were attached to the wrong orders. It was a complete mess. So - I took a 6 year old copy of the database where all relations are in tact, and created a brand new DB from that. I wanna thank you people for patience as I worked through this. As always - you folks are awesome.
There were considerable changes made to product codes that would have diminished the integrity of the relationships. So I am thinking that as changes were made by the end user, they might have been prompted by messages like "You cannot change this record because there are related records in other tables, unless you break the relationship first. Would you like to break that relationship now?" to which they answered YES. Upon checking the relationships, I found several key relationships missing. Among them two major relations between CusTrx, CusTrxItems, and Customers. There were episodes where Customers were attached to the wrong orders. It was a complete mess. So - I took a 6 year old copy of the database where all relations are in tact, and created a brand new DB from that. I wanna thank you people for patience as I worked through this. As always - you folks are awesome.
ASKER
Can I divy up the solution points between you folks - not sure how that option works in this new format. I dont want anyone to go unrewarded for any contribution because if I hadn't gone through all that - I might never have looked at the relationships
You can select as many comments as you want as "This is my solution", or This was helpful. When you close out the question, you should also be able to assign additional points for "above and beyond" levels of support by using the slider bar next to each persons name.
<<When you close out the question, you should also be able to assign additional points for "above and beyond" levels of support by using the slider bar next to each persons name.>>
There's no closing of questions anymore.
Also, one other point for dawber39, you can do your own comments as well. In this case, you found the solution and the Expert's only got you pointed in the right direction (which obviously is worth something).
Note however that you will not receive "Expert" points for accepting your own comment as solution.
Jim.
There's no closing of questions anymore.
Also, one other point for dawber39, you can do your own comments as well. In this case, you found the solution and the Expert's only got you pointed in the right direction (which obviously is worth something).
Note however that you will not receive "Expert" points for accepting your own comment as solution.
Jim.
<< There's no closing of questions anymore.>>
Actually, I have to take that back a bit. While they did eliminate the close process, they did add this:
So Dale, you were correct to a point. You can add additional kudo's for Experts.
Jim.
Actually, I have to take that back a bit. While they did eliminate the close process, they did add this:
So Dale, you were correct to a point. You can add additional kudo's for Experts.
Jim.
ASKER
Thanks guys - appreciate your input as always
Richard
Richard