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

Jumat, 14 November 2014

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

John,

So I added the procedure and function. I think that I am missing the strTableName in the procedure because I do not see it. Do I need that?

I am getting a Compile Error:

Ambiguous name detected:~

for the Public function.

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

Here is the procedure. I changed the Remote mdb and the table names and it still failed.

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("tblInProcessNewTask")

' 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 [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

Jim Wagner

On Friday, November 14, 2014 10:07 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

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

' 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

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.

Jim Wagner

__._,_.___

Posted by: Jim Wagner <luvmymelody@yahoo.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (28)