Assign primarykey after maketable query

[jdttek]

I am trying to figure out how to assign a primary key using code, after running a Make Table query or importing a new table. The query runs everyday to refresh a table but, because it always deletes the old table, it looses the primary key.

As alternative, I have been using Delete Table and then running an Append Query which keeps the old PKey but would prefer to create new table instead
Thanks in advance.

JDTTEK

 

[BSman]

Before deleting the old table, be sure that the new data/table that you are importing and using as a source for the make table query includes all of the current primary keys.

I can't understand why you would want to delete an existing table and re-create it as a regular procedure. That just keeps increasing the size of your database (and slowing it down, particularly due to the ever increasing number of deleted records and size). Why not update the records that are changed and append the new records to the permanent table (that you've been deleting)?

And how do you assign the primary key anyway?

 

[jdttek]

Bsman
Thanks for reply

Daily I import updated account receivable balances that have been downloaded into Excel spreadsheet. Yes, update append works (also add delete for paid balances Found that just deleting all exiting records and then pasting records from spreadsheet worked just as well, assured that table records are exactly the same as the system balances and preserve account # as primary key. DB is compacted after the process to eliminate bloat.

What I am trying to do is simply import the excel spreadsheet to existing table, which would override the existing table records. Only probblem with that is that primary key needs to be recreated (I meant "created" , not assigned). So what I need to to is make Acct# the primary key after the import process.
Same applies to MT Query where I merge two felds to create a new unique field that will be used a primary key in table. I need to make that field the Primary Key when the table is created.


I have seen several posts on sing Index and Primary key, but I can find nowhere that explains the correct sytax for Index / Primary key. Could you please point me in the right direction?

Thanks
JDTTEK

 

[cmmrfrds]

What I have done before, I guess it was the first thing to come to mind at the time, was to make a second definition of the table under a different name, then delete the table with the data, then make a copy of the 2nd defintiion then rename the copy to the original name - it works.

Another way is with the ADOX Catalog.

I will paste in a function that has code that does several things on the Catalog there are many commented lines since it was used for several purposes. Iterate through the properties collection to get the name of the property to set.

Function catalogInfoShort()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cl As ADOX.Column
Set cg.ActiveConnection = CurrentProject.Connection

''tb.Name = "Test"
''tb.Columns.Append "col1", adInteger
''tb.Columns.Append "col2", adVarWChar, 50
''Debug.Print "table = "; cg.Tables("Test&quot.Name
''cg.Tables.Append tb
'-rename a column
''Set tb = cg("test&quot
''Debug.Print "tb = "; tb.Name
''tb.Columns.Append "col3", adInteger
''cg.Tables.Append tb
''Set cl = tb("col2aa&quot
''Debug.Print cl
''cl.Properties("Jet OLEDB:Allow Zero Length&quot = True
''cl.Name = "col2aa"

Set cg.ActiveConnection = CurrentProject.Connection
Set tb = cg.Tables("test&quot
Debug.Print "table name = "; tb.Name
'''Set cg.Procedures("myproc&quot = "select * from customer"
Dim pp As Property
''Debug.Print "column = "; tb.Columns("Description&quot.Properties("default&quot.Value
''Exit Function
For Each cl In tb.Columns
Debug.Print "name = "; cl.Name
Debug.Print "type = "; cl.type
For Each pp In cl.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value

Next
Next

End Function

 

[BSman]

OK, then just do the steps in this order:

1. Delete all records in the table.
2. Import the data from the spreadsheet into the (empty) table.

Since the table definition includes the Acct# as the primary key, everything will be fine (assuming the data is correct in the spreadsheet). If you try to import before deleting the old records, then you will get errors because of duplicate Acct#'s.

Obviously, it would probably be a good idea to copy the database first before the delete and import, just in case something goes wrong during the process.

 

[jdttek]

Bsman
Thanks again for your reply. What you describe is exactly what I have been doing. Because I am deleting and copying/pasting to an existing table, the PK remains the same (Acct#).

What I am trying to do is NOT have to delete and paste, but just import new table (or create new table with MTQuery) which writes over the old by creating a new table. This creates a new table without a PK, so I need to create a new PK with code. I have seen reference to using INDEX and PRIMARY SQL stmt but can't find anything about syntax for these. Any idea how to do this?

JDTTEK