|
 |
access thread: Change Table Source name
Message #1 by "Anthony Delcy" <DELCYAN@l...> on Thu, 10 May 2001 16:09:42 -0400
|
|
Hi,
I would like to change the table source name and I am having difficulty.
Because the AS400 are going to be moved into USA I need to change all the
links that defferent departments has in their MS Access application to the
new name convention USA uses. The field name will remain the same but the
library will be changed to their standard. For example, here we have
WAJPRDDTA.F0022, WAJPRDDTA is the library and F0022 is the file. In USA
that same library will be something like WAJDTA_ST_PRO.ST_F0022 .
The following is my code but doesn't work.
Thank you
Anthony
Here is my code:
Public Function ChangeTableSourceSingle(ByVal pstrDatabasePath As String,
_
ByVal pstrOldName As String, _
ByVal pstrNewName As String) As Boolean
Dim db As Database
Dim intCount As Integer
Dim intTblCount As Integer
Dim strSQLSource As String
Dim lngFound As Long
Dim strNewSource As String
Dim objRegister As clsRegisterChanges
Dim strObjectName As String
Dim blnReturn As Boolean
Dim blnTestResult As Boolean
On Error GoTo ERR_ChangeTableSourceSingle
Set objRegister =3D New clsRegisterChanges
Set db =3D OpenDB_File(pstrDatabasePath)
intTblCount =3D db.TableDefs.Count
For intCount =3D 0 To intTblCount
strSQLSource =3D db.TableDefs(intCount).SourceTableName
lngFound =3D InStr(waStringStart, strSQLSource, pstrOldName)
If lngFound > 0 Then
strNewSource =3D Replace(strSQLSource, pstrOldName, pstrNewName
)
db.TableDefs(intCount).SourceTableName =3D strNewSource
strObjectName =3D db.TableDefs(intCount).Name
''Register the changes
objRegister.StoreChanges pstrDatabasePath, waTable, strObjectNa
me, pstrOldName, strNewSource, Format(Now(), "YYYY-MM-DD HH:MM:SS"), ""
''Test the to see whether the changes made works
blnReturn =3D TestDB_File(db, intCount)
''Register only the fail result for now.
If Not blnTestResult Then
''Call the register class to log in the test result
Call objRegister.StoreAppTest(pstrDatabasePath, waTable,
strObjectName, blnTestResult, Format(Now(), "YYYY-MM-DD HH:MM:SS"))
End If
End If
Next
Set objRegister =3D Nothing
Set db =3D Nothing
Exit Function
ERR_ChangeTableSourceSingle:
Err.Raise Err.Description, Err.Source, Err.Number
Set objRegister =3D Nothing
Set db =3D Nothing
End Function
Private Function OpenDB_File(ByVal pDatabasePath As String) As Database
Dim db As Database
On Error GoTo ERR_OpenDB_File
''Open the MS Access database
Set db =3D OpenDatabase(pDatabasePath)
Set OpenDB_File =3D db
Set db =3D Nothing
Exit Function
ERR_OpenDB_File:
Set db =3D Nothing
Set OpenDB_File =3D Nothing
End Function
Anthony Delcy
MIS
delcyan@m...
Message #2 by "Pardee, Roy E" <roy.e.pardee@l...> on Thu, 10 May 2001 15:26:58 -0700
|
|
Your code is a bit hard to follow--I think there's some e-mail client
garbage mixed in there...
But I don't see a call to .RefreshLink after you set the new source table
name--try adding that.
HTH,
-Roy
-----Original Message-----
From: Anthony Delcy [mailto:DELCYAN@l...]
Sent: Thursday, May 10, 2001 3:06 PM
To: Access
Subject: [access] Change Table Source name
Hi,
I would like to change the table source name and I am having difficulty.
Because the AS400 are going to be moved into USA I need to change all the
links that defferent departments has in their MS Access application to the
new name convention USA uses. The field name will remain the same but the
library will be changed to their standard. For example, here we have
WAJPRDDTA.F0022, WAJPRDDTA is the library and F0022 is the file. In USA
that same library will be something like WAJDTA_ST_PRO.ST_F0022 .
The following is my code but doesn't work.
Thank you
Anthony
Here is my code:
Public Function ChangeTableSourceSingle(ByVal pstrDatabasePath As String,
_
ByVal pstrOldName As String, _
ByVal pstrNewName As String) As Boolean
Dim db As Database
Dim intCount As Integer
Dim intTblCount As Integer
Dim strSQLSource As String
Dim lngFound As Long
Dim strNewSource As String
Dim objRegister As clsRegisterChanges
Dim strObjectName As String
Dim blnReturn As Boolean
Dim blnTestResult As Boolean
On Error GoTo ERR_ChangeTableSourceSingle
Set objRegister =3D New clsRegisterChanges
Set db =3D OpenDB_File(pstrDatabasePath)
intTblCount =3D db.TableDefs.Count
For intCount =3D 0 To intTblCount
strSQLSource =3D db.TableDefs(intCount).SourceTableName
lngFound =3D InStr(waStringStart, strSQLSource, pstrOldName)
If lngFound > 0 Then
strNewSource =3D Replace(strSQLSource, pstrOldName, pstrNewName
)
db.TableDefs(intCount).SourceTableName =3D strNewSource
strObjectName =3D db.TableDefs(intCount).Name
''Register the changes
objRegister.StoreChanges pstrDatabasePath, waTable, strObjectNa
me, pstrOldName, strNewSource, Format(Now(), "YYYY-MM-DD HH:MM:SS"), ""
''Test the to see whether the changes made works
blnReturn =3D TestDB_File(db, intCount)
''Register only the fail result for now.
If Not blnTestResult Then
''Call the register class to log in the test result
Call objRegister.StoreAppTest(pstrDatabasePath, waTable,
strObjectName, blnTestResult, Format(Now(), "YYYY-MM-DD HH:MM:SS"))
End If
End If
Next
Set objRegister =3D Nothing
Set db =3D Nothing
Exit Function
ERR_ChangeTableSourceSingle:
Err.Raise Err.Description, Err.Source, Err.Number
Set objRegister =3D Nothing
Set db =3D Nothing
End Function
Private Function OpenDB_File(ByVal pDatabasePath As String) As Database
Dim db As Database
On Error GoTo ERR_OpenDB_File
''Open the MS Access database
Set db =3D OpenDatabase(pDatabasePath)
Set OpenDB_File =3D db
Set db =3D Nothing
Exit Function
ERR_OpenDB_File:
Set db =3D Nothing
Set OpenDB_File =3D Nothing
End Function
Anthony Delcy
MIS
delcyan@m...
|
|
|
View Cart
