Why does a primary key go away then come back?
Hello experts, I am getting an error message "Access Error - Diamd_no is not an index" when I run the code provided. The field Diamd_no is the Primary Key for the the table, However, when the program halts and the above message is displayed, if I look at the table tmptblSeriesCodeCheck, the primary key has gone. Once I reset the vba code and look again the primary key returns. It's like playing peek-a-boo with my kid, only nowhere near as much fun. Please help!! The program halts at the second to last line and I've included a lot above as my knowledge on mixing DAO and ADODB is limited as I'm new to this. I know it's New Year Day, so I'm not expecting an answer soon. Cheers!
.Index = "DIAMD_NO"
.Index = "DIAMD_NO"
Dim conn As ADODB.Connection
Dim rstMasterData As ADODB.Recordset
Dim db As DAO.Database
Dim rstTmpTable As DAO.Recordset
Dim strSql As String
' Select current month's subscription data from tblMasterDataFileItems.
Set db = OpenDatabase(CurrentProject.Path & "\MyShop.accdb")
Set rstTmpTable = db.OpenRecordset("tmptblSeriesCodeCheck", dbOpenTable)
strSql = "SELECT tblMasterDataFileItems.DIAMD_NO, tblMasterDataFileItems.SERIES_CODE, tblMasterDataFileItems.FULL_TITLE, " & _
"tblMasterDataFileItems.PUBLISHER FROM tblControlFile, tblMasterDataFileItems WHERE ((Left([DIAMD_NO],5)=[fldCurrentMonth]+[fldcurrentYear])" & _
"AND ((tblMasterDataFileItems.SERIES_CODE)>0));"
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & CurrentProject.Path & "\MyShop.accdb"
Set rstMasterData = New ADODB.Recordset
rstMasterData.Open strSql, conn, adOpenKeyset, adLockOptimistic
' Step through the record set. If the item exists, add to the quantity, if not found, create and set quantity to one
Do While Not rstMasterData.EOF
With rstTmpTable
.Index = "DIAMD_NO"
.Seek "=", [tblMasterDataFileItems]![DIAMD_NO], adSeekFirstEQ
If you have an index that is going away, I'd suggest you have a severely corrupted database. I'd IMMEDIATELY make a backup, and then do this:
1) Compact and repair - click Office Button - Manage - Compact
2) Open the VBA Editor. Click Debug - Compile. Fix any errors. Contine to Compile until the menu option is disabled.
3) C&R again
4) Build a new, blank database
5) Import everything into that new, blank database.
6) Compile the new database. Fix any errors
7) C&R the new database
1) Compact and repair - click Office Button - Manage - Compact
2) Open the VBA Editor. Click Debug - Compile. Fix any errors. Contine to Compile until the menu option is disabled.
3) C&R again
4) Build a new, blank database
5) Import everything into that new, blank database.
6) Compile the new database. Fix any errors
7) C&R the new database
I would suggest using DAO throughout, as DAO is the best for working with Access data.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow, I'm not sure if I'm impressed with myself or more confused. The machine I'm using is new and the only thing I'm doing on it is Access Development, or attempting to anyway. So if the database is all messed up, it can only be from something that I have done through my code. (I feel like the Bart Simpson of EE all of a sudden) So I have a few questions to both of your anwers and I will split the points evenly tonight when I get home as both have highlighted what feels critical to me.
1 . Is wrecking a database something that is quite easy to do and I should be a lot more careful about the code I test? I do tend to write something and then just run it to see what happens. Time to stop this approach yes?
2. OK, I'll switch to only DAO if that is your recommendation over ADO. Before I plunge on, is this just your personal preference or is DAO the Genius standard? Might as well get it right form the start.
3. I had thought the reason for DAO and ADO is that they each have strengths over the other and that I should be mixing them. This is obviuosly not the case then?
Thanks a bunch to you both.
1 . Is wrecking a database something that is quite easy to do and I should be a lot more careful about the code I test? I do tend to write something and then just run it to see what happens. Time to stop this approach yes?
2. OK, I'll switch to only DAO if that is your recommendation over ADO. Before I plunge on, is this just your personal preference or is DAO the Genius standard? Might as well get it right form the start.
3. I had thought the reason for DAO and ADO is that they each have strengths over the other and that I should be mixing them. This is obviuosly not the case then?
Thanks a bunch to you both.
ASKER
Oh, and just to confirm, regading the whole compact/repair/new database etc, I've split my system into two. One with just the tables and one with everything else. Do I do what you are suggesting for both or just the tables database?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In regards to your databases, I'd do it on both, but your issue would be with the Backend database.
ASKER
Thanks for you help here. Using just DAO will at least halve some of the code I need to learn at this stage, so a silver lining exists!! You guys rock!!
ASKER
Thanks, as always, for your help. This stuff can be very confusing to learn from a book and online help.
ASKER