Re: [MS_AccessPros] Re: [MS AccessPros] Portable Access

 

Jim-

You should be able to tweak this code to do the remote reset of the AutoNumber value.

In a Standard module:

Public Function GetRemoteMDB(strTableName As String) As String
    'strTableName is a linked table
    GetRemoteMDB = Mid(CurrentDb.TableDefs(strTableName).Connect, 11)
End Function

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database, dbR As DAO.Database

Dim strRemoteMDB As String

'this set of code will
    'delete the records in the tblInProcessNewTask
    'alter the NewTaskID to reset to autonumber 1 each time
    'Append the records from the tasks table
    
    ' Point to this database
    Set db = CurrentDb

    ' Get the direct link to the remote database

        strRemoteDB = GetRemoteMDB("My Linked Table Name")

    ' Open it
    set dbR = Opendatabase(strRemoteMDB, , 0)

    'deletes all records in the tblInProcessNewTask table

    db.Execute "qryDeleteInProcessNewTask", dbFailOnError
        'tblInProcessNewTask

    ' Give the engine time to finish and free all locks
    DBEngine.Idle dbFreeLocks

    'alter the table to reset the autonumber for the NewTaskID field to 1 each time the form is opened.
    ' Set counter back to 1
    dbR.Execute "ALTER TABLE [Copy Of tblInProcessNewTask] ALTER COLUMN NewTaskID COUNTER (1, 1)", dbFailOnError
        'ALTER TABLE [Copy Of tblInProcessNewTask] ALTER COLUMN NewTaskID COUNTER (1, 1);

    ' Close the remote

    dbR.Close

    Set dbR = Nothing
    ' Free locks again

    DBEngine.Idle dbFreeLocks
   'append the tblInProcessNewTask table with the in process records.
    db.Execute "qryAppendtblInProcessTasks", dbFailOnError
        'tblTasks           'main table with the tasks
        'tblTaskDetails     'related table to the tblTasks. the details for the tblTasks table
        
    Set db = Nothing

DoCmd.OpenForm "frmTasks"
DoCmd.Close acForm, "frmSplash"
DoCmd.OpenForm "frmInProcessTasksMain"

End Sub

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Nov 14, 2014, at 4:38 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Duane,

The table is for a form that I use for the in process tasks. The entire code is below. I reset the Id to keep them sorted

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
'this set of code will
    'delete the records in the tblInProcessNewTask
    'alter the NewTaskID to reset to autonumber 1 each time
    'Append the records from the tasks table
   
    ' Point to this database
    Set db = CurrentDb

    'deletes all records in the tblInProcessNewTask table
    db.Execute "qryDeleteInProcessNewTask", dbFailOnError
        'tblInProcessNewTask

    ' Give the engine time to finish and free all locks
    DBEngine.Idle dbFreeLocks

    'alter the table to reset the autonumber for the NewTaskID field to 1 each time the form is opened.
    ' Set counter back to 1
    db.Execute "qryAlterInProcessNewTask", dbFailOnError
        'ALTER TABLE [Copy Of tblInProcessNewTask] ALTER COLUMN NewTaskID COUNTER (1, 1);
   
   'append the tblInProcessNewTask table with the in process records.
    db.Execute "qryAppendtblInProcessTasks", dbFailOnError
        'tblTasks           'main table with the tasks
        'tblTaskDetails     'related table to the tblTasks. the details for the tblTasks table
       
    Set db = Nothing

DoCmd.OpenForm "frmTasks"
DoCmd.Close acForm, "frmSplash"
DoCmd.OpenForm "frmInProcessTasksMain"

End Sub

 

Jim Wagner

---

On Friday, November 14, 2014 7:47 AM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

Jim,
 
You can alter a table in a back-end file by setting the "db" to the back-end database file rather than Currentdb. You could try something like the following:
 
Public Function GetRemoteMDB(strTableName As String) As String
    'strTableName is a linked table
    GetRemoteMDB = Mid(CurrentDb.TableDefs(strTableName).Connect, 11)
End Function
 
' ---- your code ----
    'alter the table to reset the autonumber for the NewTaskID field to 1 each time the form is opened.
    ' Set counter back to 1
    Dim dbRemote as DAO.Database
    Dim strRemoteMDB as String
    strRemoteMDB = GetRemoteMDB("My Linked Table Name")
    set dbRemote = Opendatabase(strRemoteMDB, , 0)
    dbRemote.Execute "qryAlterInProcessNewTask", dbFailOnError
        'ALTER TABLE [Copy Of tblInProcessNewTask] ALTER COLUMN NewTaskID COUNTER (1, 1);
 
 
Duane Hookom MVP
MS Access
 

---

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 13 Nov 2014 22:45:40 +0000
Subject: Re: [MS_AccessPros] Re: [MS AccessPros] Portable Access

 So I split the database and right away I received an error. I have  a process that runs a alter table query and the alter cannot run on a linked table. I was reading an article by J Street Technology that states that all databases should be split. The article also states tat some tables though can reside in the FE. It is a database that only I use and nobody else. The table would absolutely need to be in the database. This would be the case that the table would not be shared between two machines.

 

'alter the table to reset the autonumber for the NewTaskID field to 1 each time the form is opened.
    ' Set counter back to 1
    db.Execute "qryAlterInProcessNewTask", dbFailOnError
        'ALTER TABLE [Copy Of tblInProcessNewTask] ALTER COLUMN NewTaskID COUNTER (1, 1);

Jim Wagner

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (27)

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___