• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

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"
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

Open in new window

0
StuBabyAight
Asked:
StuBabyAight
  • 5
  • 3
  • 2
2 Solutions
 
StuBabyAightAuthor Commented:
OK, I accept it was a little bit too much hoping for a solution on New Years day.  I'll check back in 12 hours for any help.  Or as my nana would say "Never expect, always appreciate."
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
Helen FeddemaCommented:
I would suggest using DAO throughout, as DAO is the best for working with Access data.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Helen FeddemaCommented:
It may be that the mix-and-match of ADO and DAO is causing the problem.
0
 
StuBabyAightAuthor Commented:
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.  
0
 
StuBabyAightAuthor Commented:
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?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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?>

Corrupting an Access database is not too difficult to do. The approach you're using is one that many new developers use, and is really the only way to see if something works as planned.

<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.>

DAO is very tightly coupled with Access, but is not the only way to work with Access. I use a mix of both, since I use ADO exclusively when working with non-Access databases. When working with Access, I do tend to work more with DAO than ADO, but either will work. I'd say that overwhelming majority of developers use DAO when working with Access.

<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?>

I mix them all the time with no ill effects. That said, when working strictly in Access, DAO would probably be a better choice. If you ever have plans to move to a server-type platform, however, it might be wise to consider ADO

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In regards to your databases, I'd do it on both, but your issue would be with the Backend database.
0
 
StuBabyAightAuthor Commented:
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!!
0
 
StuBabyAightAuthor Commented:
Thanks, as always, for your help.  This stuff can be very confusing to learn from a book and online help.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now