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

Using VB6, how do I assign a Primary/Secondary key after creating a new table?

I'm using VB6, and I already have the code working that creates a table and assigns indexes, but I can't figure out how to create keys.
0
winterhowl
Asked:
winterhowl
  • 5
  • 4
1 Solution
 
leonstrykerCommented:
What is your database?
0
 
winterhowlAuthor Commented:
Access 97

Here's my current code:

            If CSession.ClaimType = ocm837P Then
                strSQL1 = "SELECT * INTO " & CSession.UserYNum & "_WorkingHeaderDataP " & _
                          "FROM WorkingHeaderDataP_Template"
                strSQL2 = "SELECT * INTO " & CSession.UserYNum & "_WorkingDetailDataP " & _
                          "FROM WorkingDetailDataP_Template"
                         
                strIndex1 = "CREATE INDEX GUIndex ON " & CSession.UserYNum  & "_WorkingHeaderDataP(GU)"
                strIndex2 = "CREATE INDEX YNumberIndex ON " & CSession.UserYNum & "_WorkingHeaderDataP(YNumber)"
                strIndex3 = "CREATE INDEX GUIndex ON " & CSession.UserYNum & "_WorkingDetailDataP(GU)"
                strIndex4 = "CREATE INDEX YNumberIndex ON " & CSession.UserYNum & "_WorkingDetailDataP(YNumber)"
                strIndex5 = "CREATE INDEX SeqNumIndex ON " & CSession.UserYNum & "_WorkingDetailDataP(SeqNum)"
           
            ElseIf CSession.ClaimType = ocm837I Then
                strSQL1 = "SELECT * INTO " & CSession.UserYNum & "_IWorkingHeaderData " & _
                          "FROM IWorkingHeaderData_Template"
                strSQL2 = "SELECT * INTO " & CSession.UserYNum & "_IWorkingHeaderData3070 " & _
                          "FROM IWorkingHeaderData3070_Template"
                strSQL3 = "SELECT * INTO " & CSession.UserYNum & "_IWorkingHeaderData50 " & _
                          "FROM IWorkingHeaderData50_Template"
                strSQL4 = "SELECT * INTO " & CSession.UserYNum & "_IWorkingDetailData " & _
                          "FROM IWorkingDetailData_Template"
            End If
                         
            If CSession.ClaimType = ocm837P _
             Or CSession.ClaimType = ocm837I Then
                CDE.Commands.Item("CreateTable").CommandText = strSQL1
                CDE.Commands.Item("CreateTable").Execute
                             
                CDE.Commands.Item("CreateTable").CommandText = strSQL2
                CDE.Commands.Item("CreateTable").Execute
                             
                CDE.Commands.Item("CreateTable").CommandText = strIndex1
                CDE.Commands.Item("CreateTable").Execute

                CDE.Commands.Item("CreateTable").CommandText = strIndex2
                CDE.Commands.Item("CreateTable").Execute

                CDE.Commands.Item("CreateTable").CommandText = strIndex3
                CDE.Commands.Item("CreateTable").Execute

                CDE.Commands.Item("CreateTable").CommandText = strIndex4
                CDE.Commands.Item("CreateTable").Execute

                CDE.Commands.Item("CreateTable").CommandText = strIndex5
                CDE.Commands.Item("CreateTable").Execute
            End If
0
 
leonstrykerCommented:
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
winterhowlAuthor Commented:
Thanks Leon.  I'm going to give this a try.  Unfortunately, I'm maintaining an app that I didn't write, so ADOX wasn't used, and I don't have the time to change all the DB code to ADOX.  I'm thinking I can create the temp tables I need using ADOX, then open them later on using regular ADO.  Is that a correct assumption?
0
 
leonstrykerCommented:
>I'm thinking I can create the temp tables I need using ADOX, then open them later on using regular ADO.  Is that a correct assumption?

There really are no such things as temp tables in Access.  You can however create tables query them and then destroy the tables.  Why do you want to do it this way though?
0
 
winterhowlAuthor Commented:
There are working tables this application uses to populated transaction data from an X12 format to a flat file format (working tables).  The data from the working tables is then displayed on a form.  Problem we're encountering is caused by multiple users trying to update the working tables at the same time.  They keep receiving locking errors and are growing impatient.  I thought creating personal working tables on fly would be the best solution to quickly solve the problem until we can convert the database to SQL Server.  I already checked the lock option in the database properties, and it's set to No Locks.  I can't find anything at the table level.
0
 
leonstrykerCommented:
What kind of locking are you using on your connection object?  Take a look here:

http://www.activeserverpages.ru/ADO/daprop04.htm

The problem with what you are trying to do is that user A may overwritte something which user B just modified.

Leon
0
 
winterhowlAuthor Commented:
Optimistic was used.
The working tables have a GUID and UserID field, that assures users do not overwrite each other in the working tables.  Then, in the main transaction table, the original transaction is copied, with a new GUID, then updated with appropriately.  So it's written in a way that overwriting won't occur, but I've been having trouble with the tables locking.  I'm thinking that Access97 ignores the locking options you select and just handles it the way it wants to.  That's probably not the case, but...

Is there a specific reference I need to select in order to use ADOX?
0
 
leonstrykerCommented:
To use ADOX you need to reference "Microsoft ADO Ext. 2.1 for DDL and Security"  (msadox.ddll).

>I'm thinking that Access97 ignores the locking options you select and just handles it the way it wants to.

I do not think it is ignoring them, but there maybe default settings which you are not aware of.

Leon

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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