Restore a table index using VBA

[aziz rasul]

I have a table called "tblCARFiles" with a single index called PrimaryKey.

If another user goes into the table in normal view and changes the order of how the records are sorted using A-Z or Z-A methods, how can I revert back to the original index using code before I import new records (using VBA) from an external file.

 

[Rich]

Is it not a bad idea to let "users" into the table in the first place?

 

[aziz rasul]

The problem is that the table is linked and used by several departments and hence it's unavoidable.

 

[Fornatian]

Aziz,

It's not unavoidable, an easy work around would be to build a query with no criteria that the departments can view and sort to their hearts content without disrupting the sort order of your original table.

Would this not work?

Ian

 

[aziz rasul]

Thanks for your input guys.

Fornation

U r right in what u said. However u can't prevent someone from going into to table direct.

Pat

I understood what u said. The problem is that I have no control over the table in question. Placing an autonumber field would solve the problem as u said. However if I can't place an autonumber, what happens then? In short it doesn't work. Hence the reason why I'm trying to find a VBA way of forcing the sort sequence before appending.

Note that when the data is imported, some data is written in 3 fields in the first and last records of the import. Since the sort sequence can potentially change, the written data goes to the wrong records in the table when I use the AbsolutePosition property of a recordset.

Here's part of the code which will help to understand what the problem.

Public Function Process_CAR_Files()

Dim MyDB As Database
Dim rstCARFiles As Recordset
Dim Count1 As Long, Count2 As Long
Dim DateStamp As Date

Set MyDB = CurrentDb

strImportDir = "w:\collect\"
strFileExtension = "*.car"

'Obtains the name of the CAR file
strFileName = Dir(strImportDir + strFileExtension)

If Len(strFileName) > 0 Then

strFileName = Dir(strImportDir + strFileExtension)

Do
strFileFullPath = strImportDir + strFileName
'Extracts the date and time that the CAR file was created
DateStamp = FileDateTime(strFileFullPath)

Set rstCARFiles = MyDB.OpenRecordset("tblCARFiles", dbOpenDynaset)
If rstCARFiles.RecordCount > 0 Then
rstCARFiles.MoveLast
End If
'Counts the number of records in "tblCARFiles"
Count1 = rstCARFiles.RecordCount
rstCARFiles.Close

'Imports and appends the contents of the CAR file into "tblCARFiles"
DoCmd.TransferText acImportFixed, _
"spc_IE_CARFiles", _
"tblCARFiles", _
strFileFullPath

Set rstCARFiles = MyDB.OpenRecordset("tblCARFiles", dbOpenDynaset)
If rstCARFiles.RecordCount > 0 Then
rstCARFiles.MoveLast
End If
Count2 = rstCARFiles.RecordCount

rstCARFiles.AbsolutePosition = Count1
rstCARFiles.Edit
rstCARFiles("DateofCARFile") = DateStamp
rstCARFiles("DateofImport") = Now
rstCARFiles("NameofCARFile") = strFileName
rstCARFiles.Update
rstCARFiles.AbsolutePosition = Count2 - 1
rstCARFiles.Edit
rstCARFiles("DateofCARFile") = DateStamp
rstCARFiles("DateofImport") = Now
rstCARFiles("NameofCARFile") = strFileName
rstCARFiles.Update
rstCARFiles.Close

Loop Until Len(strFileName) = 0
Else
MsgBox "No CAR files are available"
End If

End Function

[This message has been edited by aziz rasul (edited 04-09-2001).]

 

[KDg]

Hi Aziz

I agree with everything above, it's bad to let people into tables and I'm not sure that the below will really help with the ordering. Additionaly I can't really see what you're achieving with the ordering of the recordset anyway, you really should make the order an intrinsic feature/field of each record rather than rely on where it is stored, as Pat says.

Anyway, it's nothing to do with me how you make your db so - if it's causing you problems and has only been added on by filter then you can remove it via

DoCmd.OpenTable "YourTable"
SendKeys "%+rr"
DoCmd.Close acTable, "YourTable"

I guess you could do that directly before the import and it should help(?)

not pretty i agree, but it should work

HTH

Drew

[This message has been edited by KDg (edited 04-09-2001).]

 

[aziz rasul]

Drew,

Thanks for your input. I will try it.
Just for your info, the problem was that if the sort was changed, the data that I was writing to the table was appearing in the wrong records. If the sort was as I wanted it, then the data was written in the correct position.
I didn't want a system whereby I would have to go into a table (owned by someone else) and sort the records before running the code.

I will try what u suggested. If it doesn't work, I will post back. Meantime thanks.

Aziz

 

[aziz rasul]

Drew,

I can't get the SendKeys statement. Anyone know how to make it work?

 

[KDg]

hmm, which version of Access are you on? it works on '97 - if you're on 2k then what you're looking for is Remove Filter/Sort, it used to be off the Records menu. You should just be able to change the 2 'r's to whatever it is now.
If it's not the version then you'll need to get up the menu bar, if it's neither of these or u have problems post back,

Drew

 

[aziz rasul]

Drew,

I'm using 97. However when I run your code, and then check the table, the previous sort remains intact. I would have expected the sort to change. Am I misunderstanding something here? Am I slowly losing my own plot?

If I create a macro to do the job, then it works. But it doesn't work using VBA? Does that make sense?

[This message has been edited by aziz rasul (edited 04-12-2001).]