Got a good one here.

[Mendel]

In short I have code that takes a main table and then copies the data to a backup table. I make changes to the main table. Then I delete the main table and copy the data from the backup table back into the main table. My problem is that after I've copied the data from the backup table back into the main table, I can't use or reference the main table because it says the the records have been deleted.
I've tried to requery the main table but Access 2000 says that the operation cannot be performed for that object. I'm not sure if the table needs refreshed somehow but what can I do to get access to the records from the main table again? Thanks for any help.

 

[-td-shadow]

Sometimes using queries gets rid of frustration. Have you tried using make-table, delete and append queries? I have found that sometimes it's just better to use them than to hassle with programming the constant data shifting between tables.

 

[raskew]

Think this through:

(1) Make a backup of the main table.
(2) Make changes to the main table.
(3) Delete the main table (along with the changes you've just made). Presto, no main table, no changes! You've just removed the main table from your database's tabledefs!
(4) "Copy" the backup (Step 1) into the nonexistent main table, and you're surprised it doesn't work? You deleted the main table in Step 3. It doesn't exist anymore. And, if it did, you would be returning to a point before Step 1.

Need to rethink this strategy.


[This message has been edited by raskew (edited 02-12-2002).]

 

[Oldsoftboss]

Are you deleting the table or the records from the table?

 

[Mendel]

I'll admit that I'm not the most prolific programmer but as far as the deletion of the main table I'm deleting the records, not the table. I apologize for mis-stating the problem in the 1st place.

With these suggestions then am I correct to assume that it would work if I did the following:

1) Create main table
2) Create backup table
3) Make changes to records in main table and do analysis
4) Delete main table (delete table completely)
5) Create new main table (copy of backup table)

And would this work if I looped it over many times? I really appreciate all your input. Thanks.

 

[raskew]

Mendel-
Have you given thought to the fact that programming just may not be your strong point and that your talents lie in other areas?

Let's look at your latest proposal:

1) Create main table
2) Create backup table
3) Make changes to records in main table and do analysis
4) Delete main table (delete table completely)
5) Create new main table (copy of backup table)

So, where are you? Right back at Step 1. Your new main table is an exact duplicate of your original main table.

What's the point? You can loop through this a zillion times and the outcome is always going to be the same.

Need to rethink your strategy!!! (or look for another line of work)

 

[Rich]

Mendel what exactly are you trying to do and for what purpose, what is your reasoning behind making changes and then discarding them?

 

[Rich_Lovina]

Now that's a good question...

 

[BukHix]

The scary thing to me is that I have code that does something similar to the request:

    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet acImport, 8, "Jobs", _
        "H:\New Jobs\jobs.xls", False
    DoCmd.OpenQuery "JobNumbersUpdateTable", acNormal, acEdit
    DoCmd.DeleteObject acTable, "Jobs"
    DoCmd.SetWarnings True
    
    Dim objDB As Database
    Dim objRS As Recordset
    Dim AllowedCharacters As String
    Dim TempString As String
    Dim ctr As Integer

    AllowedCharacters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789-"
    Set objDB = CurrentDb()
    Set objRS = objDB.OpenRecordset("JobNumbers", dbOpenDynaset)
    
    While Not objRS.EOF
        
    TempString = ""
    For ctr = 1 To Len(objRS![JobNumber])
    If InStr(1, AllowedCharacters, Mid(objRS![JobNumber], ctr, 1)) <> 0 Then _
        TempString = TempString & Mid(objRS![JobNumber], ctr, 1)
    
    
    Next
    'Now Edit the table and replace the value of [FieldName] with TempString
    
    With objRS
        .edit
        ![JobNumber] = TempString
        .Update
        .MoveNext
    End With

    Wend
End Sub

I import a spreadsheet creating a table, then I use a make table query to (delete) overwrite the existing table with my import, last of all I delete the newly imported table. In the end I am left with a table that has the most current data.

[This message has been edited by BukHix (edited 02-14-2002).]

 

[raskew]

Mendel-

As I look at this once again, I think I've abused you unfairly. It would appear that there's a logic in what you're trying to do that just isn't coming accross. Would you please take a look at what you're trying to accomplish and work at restating the problem.

Best wishes,

Bob