By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 2,052 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

How to create custom data migration query?

P: n/a
If I release a new version of my mbd (in mde format) and users want to
upgrade - how do they migrate their data?

For example, if the original was released as data1.mde and then I release
data2.mde (same table/data structures - just revised VBA code with perhaps a
new/updated form or two), what's the best way for users to get their data
into the new data2.mde?

I'm thinking I could use a query - something like:

"INSERT [everything] FROM [data1] INTO [data2] EXCEPT WHERE [data1_column] =
AutoNumber"

This means I would have to iterate through each of the 50 tables in the
database somehow. Is this the best way? Other options?

Thanks in advance.
Nov 12 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
rkc

"deko" <de**@hotmail.com> wrote in message
news:Sw*******************@newssvr27.news.prodigy. com...
If I release a new version of my mbd (in mde format) and users want to
upgrade - how do they migrate their data?

For example, if the original was released as data1.mde and then I release
data2.mde (same table/data structures - just revised VBA code with perhaps a new/updated form or two), what's the best way for users to get their data
into the new data2.mde?

I'm thinking I could use a query - something like:

"INSERT [everything] FROM [data1] INTO [data2] EXCEPT WHERE [data1_column] = AutoNumber"

This means I would have to iterate through each of the 50 tables in the
database somehow. Is this the best way? Other options?


Your data and front end objects should reside in separate files whether
you have a single user or 30 users. Your current problem disappears.


Nov 12 '05 #2

P: n/a
> Your data and front end objects should reside in separate files whether
you have a single user or 30 users. Your current problem disappears.


What do you mean? How do I separate front end objects form data?

The other thing I tried was this:

After the new release (data2) is ready to go, delete all the tables and
relationships, and then make it into an mde. Then, import all the tables
from data1 into data2. The only problem with this is I can't update or add
any new tables in the new release, which is very limiting.
Nov 12 '05 #3

P: n/a
"deko" <de**@hotmail.com> wrote in message
news:9W*******************@newssvr25.news.prodigy. com...
Your data and front end objects should reside in separate files whether
you have a single user or 30 users. Your current problem disappears.


What do you mean? How do I separate front end objects form data?


Access can link to tables in other files. You carry this to its extreme and set
up ALL of your tables as links to another file and presto, one file for data and
one file for everything else.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #4

P: n/a
> Access can link to tables in other files. You carry this to its extreme
and set
up ALL of your tables as links to another file and presto, one file for data and one file for everything else.


So then do I distribute two mde files? One with tables and queries only,
and the other with all the forms, modules, reports, etc?

Still, if I want to update the data structure in a new release, don't I
still have the same problem?
Nov 12 '05 #5

P: n/a
"deko" <de**@hotmail.com> wrote in message
news:W3*******************@newssvr25.news.prodigy. com...
Access can link to tables in other files. You carry this to its extreme and set
up ALL of your tables as links to another file and presto, one file for

data and
one file for everything else.


So then do I distribute two mde files? One with tables and queries only,
and the other with all the forms, modules, reports, etc?


There is no benefit to making an MDE of the data file as it does nothing to
tables or queries.
Still, if I want to update the data structure in a new release, don't I
still have the same problem?


Yes. In those cases I usually include code in the new front end that
programmatically applies the table structure changes to the existing back end.
Still, the vast majority of updates don't affect the tables.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #6

P: n/a
> > So then do I distribute two mde files? One with tables and queries
only,
and the other with all the forms, modules, reports, etc?
There is no benefit to making an MDE of the data file as it does nothing

to tables or queries.


Agreed, but the idea is to distrubute 2 files: one (mde) that contains front
end (forms, modules, reports) and another (mdb) that contains the back end
(tables, relationships, queries) - is this correct?

But how to set up the links to the back end? Don't I need dummy tables in
the mde which link to the real tables in the mdb?

When all is said and done, it appears there's really not much advantage in
doing this. As I mentioned in an earlier post, I can just distribute the
new data2.mde without any tables and have the users import all the tables
from the old data1.mde. The only advantage of having separate front and
back end files is avoiding the need to do the import. When this is weighed
against the ease of maintaining only one file, I'm not sure if it's worth
it. The problem of distributing an updated version that requires changes to
the existing data structure remains an issue in either case. As you say,
that must be addressed with code in the new release.

Nov 12 '05 #7

P: n/a
> > Still, if I want to update the data structure in a new release, don't I
still have the same problem?
Yes. In those cases I usually include code in the new front end that
programmatically applies the table structure changes to the existing back

end. Still, the vast majority of updates don't affect the tables.


So, if a new release (mde) includes changes to the table/data structure -
regardless of whether or not the front and back ends are split - I'll need
code to massage the data from the old structures into the new structures.
If this is the case, then why bother splitting into front and back end?

As for getting existing data into a new mde version WITHOUT data structure
changes, why not simply gin up some code to do this as well? I could
distribute the updated mde with code that runs once the first time it's
opened that prompts for the path to the old mde, and then imports all the
tables automaticcally with a few DoCmd.TransferDatabase statements. This
seems like a better way to go than having to deal with two files.
Nov 12 '05 #8

P: n/a
From: http://support.microsoft.com/?kbid=304932
Reasons Why You May Want to Split Your Database
The following are typical reasons to split a database:

You are sharing your database with multiple users on a network.
You have several people developing in the database and you do not have
Microsoft Visual Source Safe installed.
You do not want your users to be able to make design changes to tables.

The most common reason to split a database is that you are sharing the
database with multiple users on a network. If you simply store the database
on a network share, when your users open a form, query, macro, module, or
report, these objects have to be sent across the network to each individual
who uses the database. If you split the database, each user has their own
copy of the forms, queries, macros, modules, and reports. Therefore, the
only data that must be sent across the network is the data in the tables.

Bottom line: if you want to follow best practices in a multi-user
environment, split the database; in a single-user environment, don't split.
Nov 12 '05 #9

P: n/a
I have an app which I have sold to a number of single users.
It is split. All tables are in the back end. Every thing else is in the front
end.
The back end is unique to each user. The front ends are all identical.

Upgrades are simply a matter of distributing a new front end.
Include some code with the front end which relinks the tables
No problems !!
HTH
David B

deko <de**@hotmail.com> wrote in message
news:7k******************@newssvr29.news.prodigy.c om...
From: http://support.microsoft.com/?kbid=304932
Reasons Why You May Want to Split Your Database
The following are typical reasons to split a database:

You are sharing your database with multiple users on a network.
You have several people developing in the database and you do not have
Microsoft Visual Source Safe installed.
You do not want your users to be able to make design changes to tables.

The most common reason to split a database is that you are sharing the
database with multiple users on a network. If you simply store the database
on a network share, when your users open a form, query, macro, module, or
report, these objects have to be sent across the network to each individual
who uses the database. If you split the database, each user has their own
copy of the forms, queries, macros, modules, and reports. Therefore, the
only data that must be sent across the network is the data in the tables.

Bottom line: if you want to follow best practices in a multi-user
environment, split the database; in a single-user environment, don't split.


Nov 12 '05 #10

P: n/a
rkc

"deko" <de**@hotmail.com> wrote in message
news:7k******************@newssvr29.news.prodigy.c om...
From: http://support.microsoft.com/?kbid=304932
Reasons Why You May Want to Split Your Database
The following are typical reasons to split a database: <snip>
Bottom line: if you want to follow best practices in a multi-user
environment, split the database; in a single-user environment, don't

split.

The final decision is yours to make. At least now you are aware of the
option.
Nov 12 '05 #11

P: n/a
> > Bottom line: if you want to follow best practices in a multi-user
environment, split the database; in a single-user environment, don't

split.

The final decision is yours to make. At least now you are aware of the
option.


I still need a way to for users to easily upgrade to new versions. This
code imports all tables and relationships from the previous version, which
seems to do the trick.

When distributing a new version of the mde, I delete all the tables and set
the Startup Form to "frmUpgrade", which has code to set the Startup Form
back to what it should be after the upgrade is completed. When the new mde
is first opened, the user is prompted for the previous version number - so I
can add code to accommodate upgrades from different versions.

[Form_frmUpgrade]
Private Sub cmdImportData_Click()
Dim db As Object
If Not IsNumeric(Me.txtOldver) Or IsNull(Me.txtOldver) Then Exit Sub
'add code here to accommodate different versions, or if
'data structures need to be changed, or other
'version-specific stuff needs to be done to upgrade
If modUpgrade.ImportData Then
MsgBox ("Data Import Successful")
DoCmd.Close acForm, "frmUpgrade"
Set db = Application.CurrentDb
db.Properties("StartupForm") = "frm0"
DoCmd.OpenForm "frm0"
Else
MsgBox ("Data Import Failed")
End If
End Sub

ImportData does all the work - code adapted from a Microsoft KB article.

[modUpgrade]
Public Function ImportData() As Boolean
On Error GoTo HandleErr
Dim db As DAO.Database
Dim cdb As DAO.Database
Dim tdf As TableDef
Dim rel As Relation
Dim nrel As Relation
Dim r, t As Integer
Dim strTDef As String
Dim strRName As String
Dim strTName As String
Dim fld As Field
Dim strFTName As String
Dim varAtt As Variant
Dim strFName As String
Dim strFFName As String
Set cdb = CurrentDb
Set db = DBEngine.Workspaces(0).OpenDatabase(Oldver, True)
For Each tdf In db.TableDefs
strTDef = tdf.Name
If Left(strTDef, 4) <> "MSys" Then
DoCmd.TransferDatabase acImport, "Microsoft Access", Oldver,
acTable, _
strTDef, strTDef, False
End If
Next
cdb.TableDefs.Refresh
For Each rel In db.Relations
With rel
'get properties of relation to copy.
strRName = .Name
strTName = .Table
strFTName = .ForeignTable
varAtt = .Attributes
'create relation in current db with same properties
Set nrel = cdb.CreateRelation(strRName, strTName, strFTName,
varAtt)
For Each fld In .Fields
strFName = fld.Name
strFFName = fld.ForeignName
nrel.Fields.Append nrel.CreateField(strFName)
nrel.Fields(strFName).ForeignName = strFFName
Next
cdb.Relations.Append nrel
End With
Next
cdb.Relations.Refresh
ImportData = True
Set db = Nothing
Set cdb = Nothing
End Function

Oldver takes the version number entered on frmUpgrade

[modUpgrade]
Private Function Oldver() As String
On Error GoTo HandleErr
Dim strDBPath As String
Dim strDBFile As String
Dim strOldver As String
strOldver = "data" & Forms("frmUpgrade").txtOldver & ".mde"
strDBPath = CurrentDb.Name
strDBFile = Dir(strDBPath)
Oldver = Left$(strDBPath, Len(strDBPath) - Len(strDBFile)) & strOldver
End Function
Nov 12 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.