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
Jim Wagner
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);
' Set counter back to 1
db.Execute "qryAlterInProcessNewTask", dbFailOnError
'ALTER TABLE [Copy Of tblInProcessNewTask] ALTER COLUMN NewTaskID COUNTER (1, 1);
Jim Wagner
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (25) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar