updating a table from another mdb file

  • Thread starter Thread starter Tom Hanley
  • Start date Start date
T

Tom Hanley

I have an application that uses several tables and I constantly revise the app and want to update the mdb to the new version. I have been using copy and paste to do this. I plan on splitting the program at some point but not yet. I have to copy and overwrite the records in the new program with records from the old program. is there a utility to do this where I choose old table and new table to migrate the data?
 
forgot to mention that these are access 97 versions because they are being used in AutoCAD.
I have an application that uses several tables and I constantly revise the app and want to update the mdb to the new version. I have been using copy and paste to do this. I plan on splitting the program at some point but not yet. I have to copy and overwrite the records in the new program with records from the old program. is there a utility to do this where I choose old table and new table to migrate the data?
 
We usually talk about creating a table, updating records in a table, or
appending records to a table. "overwrite" is a bit brutal and unclear.

To "overwrite" a table one might delete the old one and import the new one,
renaming it if necessary.
Or one might delete all of the records within it and append all of the
records from the new table.
To "overwrite" some records one would update those present and then probably
append any new records. This involves two queries, an Update query and an
append query.

I have an application that uses several tables and I constantly revise the
app and want to update the mdb to the new version. I have been using copy
and paste to do this. I plan on splitting the program at some point but not
yet. I have to copy and overwrite the records in the new program with
records from the old program. is there a utility to do this where I choose
old table and new table to migrate the data?
 
Assuming the table names in each database are the same and they are
identically structured you can do this quite easily with a simple little
procedure in the new database which first empties the new local table and
then populates it with the rows from the external table:

Public Sub UpdateTable(strSource As String, strTable As String)

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

' empty local table
strSQL = "DELETE * FROM " & strTable
dbs.Execute strSQL

' fill local table from source database
strSQL = "INSERT INTO " & strTable & _
" SELECT * FROM " & strTable & _
" IN """ & strSource & """"
dbs.Execute strSQL

End Sub

Paste this into a standard module in the new database and call it, passing
the path to the old database and the name of the table like so:

UpdateTable "F:\SomeFolder\SomeSubFolder|Somedatabase.mdb", "SomeTable"

To make it as easy as possible you could design a dialog form with a combo
box which lists the table names so all you have to do is select one, a text
box to enter the path to the source database in and a button to import the
data by means of code along these lines in its Click event procedure:

If Not IsNull(Me.txtSource) Then
If Not IsNull(Me.cboTable) Then
UpdateTable Me.txtSource, Me.cboTable
End If
End If

where txtSource is the text box with the path to the source database, and
cboTable the combo box of table names.

To make things even easier you could have a Browse button on the form to
open a common dialog in which you could select the source database and insert
the path into the txtSource text box. There are a million and one places you
can download code for opening a common dialog from. The following has some
code produced by Ken Getz for instance:


http://www.mvps.org/access/api/api0001.htm


though I generally use Bill Wilson's class module available from:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=22415&webtag=ws-msdevapps


Using the latter the code for the Browse button would be:

Dim OpenDlg As New BrowseForFileClass

OpenDlg.DialogTitle = "Select Source Database"
OpenDlg.DefaultType = "*.mdb"

Me.txtSource = OpenDlg.GetFileSpec

Set OpenDlg = Nothing

Ken Sheridan
Stafford, England
 
Thx Ken...

Ken Sheridan said:
Assuming the table names in each database are the same and they are
identically structured you can do this quite easily with a simple little
procedure in the new database which first empties the new local table and
then populates it with the rows from the external table:

Public Sub UpdateTable(strSource As String, strTable As String)

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

' empty local table
strSQL = "DELETE * FROM " & strTable
dbs.Execute strSQL

' fill local table from source database
strSQL = "INSERT INTO " & strTable & _
" SELECT * FROM " & strTable & _
" IN """ & strSource & """"
dbs.Execute strSQL

End Sub

Paste this into a standard module in the new database and call it, passing
the path to the old database and the name of the table like so:

UpdateTable "F:\SomeFolder\SomeSubFolder|Somedatabase.mdb", "SomeTable"

To make it as easy as possible you could design a dialog form with a combo
box which lists the table names so all you have to do is select one, a
text
box to enter the path to the source database in and a button to import the
data by means of code along these lines in its Click event procedure:

If Not IsNull(Me.txtSource) Then
If Not IsNull(Me.cboTable) Then
UpdateTable Me.txtSource, Me.cboTable
End If
End If

where txtSource is the text box with the path to the source database, and
cboTable the combo box of table names.

To make things even easier you could have a Browse button on the form to
open a common dialog in which you could select the source database and
insert
the path into the txtSource text box. There are a million and one places
you
can download code for opening a common dialog from. The following has
some
code produced by Ken Getz for instance:


http://www.mvps.org/access/api/api0001.htm


though I generally use Bill Wilson's class module available from:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=22415&webtag=ws-msdevapps


Using the latter the code for the Browse button would be:

Dim OpenDlg As New BrowseForFileClass

OpenDlg.DialogTitle = "Select Source Database"
OpenDlg.DefaultType = "*.mdb"

Me.txtSource = OpenDlg.GetFileSpec

Set OpenDlg = Nothing

Ken Sheridan
Stafford, England
Click to expand...
 
<picky>
In case the name of the table has embedded blanks in it, you might want to
use:

Public Sub UpdateTable(strSource As String, strTable As String)

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

' empty local table
strSQL = "DELETE * FROM [" & strTable & "]"
dbs.Execute strSQL

' fill local table from source database
strSQL = "INSERT INTO [" & strTable & _
"] SELECT * FROM [" & strTable & _
"] IN """ & strSource & """"
dbs.Execute strSQL

End Sub
</picky>
 
Doug:

Good point. Never putting spaces or other special characters in object
names myself, I do tend to forget that others might.

Ken Sheridan
Stafford, England

Douglas J. Steele said:
<picky>
In case the name of the table has embedded blanks in it, you might want to
use:

Public Sub UpdateTable(strSource As String, strTable As String)

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

' empty local table
strSQL = "DELETE * FROM [" & strTable & "]"
dbs.Execute strSQL

' fill local table from source database
strSQL = "INSERT INTO [" & strTable & _
"] SELECT * FROM [" & strTable & _
"] IN """ & strSource & """"
dbs.Execute strSQL

End Sub
</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ken Sheridan said:
Assuming the table names in each database are the same and they are
identically structured you can do this quite easily with a simple little
procedure in the new database which first empties the new local table and
then populates it with the rows from the external table:

Public Sub UpdateTable(strSource As String, strTable As String)

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

' empty local table
strSQL = "DELETE * FROM " & strTable
dbs.Execute strSQL

' fill local table from source database
strSQL = "INSERT INTO " & strTable & _
" SELECT * FROM " & strTable & _
" IN """ & strSource & """"
dbs.Execute strSQL

End Sub

Paste this into a standard module in the new database and call it, passing
the path to the old database and the name of the table like so:

UpdateTable "F:\SomeFolder\SomeSubFolder|Somedatabase.mdb", "SomeTable"
Click to expand...
Click to expand...
 
Back
Top