VBA -

[MVP - WannaB]

Hello, I have 2 nearly identical MDB files, one in production and the copy
in dev. I would like to automate the process of pulling fresh data into the
dev db and the only thing I can think of is to copy the Production db to the
same folder as the dev db, create links to each table, and then build update
and append queries to pull the data into the dev from the production. My
question is, How can this be done without all those extra objects, just
using VBA ??
Thank you.

 

[Douglas J. Steele]

You can refer to tables in other databases in queries without having to link
the tables.

For instance, you could use

INSERT INTO MyTable (ID, Field1, Field2, Field3)
SELECT ID, Field1, Field2, Field3
FROM [;DATABASE=E:\Folder\File.mdb].MyTable
WHERE ID NOT IN (SELECT DISTINCT ID FROM MyTable)

You might also find my November, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access" to be relevant to what you're trying to do. I
show how to write a single query that will insert new rows and update
existing ones. You can download the column (and sample database) for free
from http://www.accessmvp.com/DJSteele/SmartAccess.html

That having been said, though, can you not just replace the development
database with the production one?

 

[MVP - WannaB]

Thanks Doug. You say "can you not just replace the development
database with the production one". Did I imply that I was replacing the
development database with the production database, if so my mistake. What I
think I can do and want to do is replace the data in the dev db with the
data in the production db. So as I am working in the dev db I have current
data. I don't know of any reason that I shouldn't or couldn't do that, is
there one? Please elaborate.
=============================
You can refer to tables in other databases in queries without having to link
the tables.

For instance, you could use

INSERT INTO MyTable (ID, Field1, Field2, Field3)
SELECT ID, Field1, Field2, Field3
FROM [;DATABASE=E:\Folder\File.mdb].MyTable
WHERE ID NOT IN (SELECT DISTINCT ID FROM MyTable)

You might also find my November, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access" to be relevant to what you're trying to do. I
show how to write a single query that will insert new rows and update
existing ones. You can download the column (and sample database) for free
from http://www.accessmvp.com/DJSteele/SmartAccess.html

That having been said, though, can you not just replace the development
database with the production one?

 

[Larry Linson]

Thanks Doug. You say "can you not just replace the development
database with the production one". Did I imply that I was replacing the
development database with the production database, if so my mistake. What
I
think I can do and want to do is replace the data in the dev db with the
data in the production db. So as I am working in the dev db I have
current
data. I don't know of any reason that I shouldn't or couldn't do that, is
there one? Please elaborate.

You should have your database split into a front-end (queries, forms,
reports, macros, and modules) linked to tables in a back-end (tables with
data, and relationships). You will still have a dev copy (the one you are
changing) and a production copy (the last one released to the users) of the
front-end and a dev copy and production copy of the back end. The vast
majority of changes and enhancements to applications are only to the
front-end.

In this case, to "promote" your dev copy (after appropriate testing) to
production status, you simply relink it from the dev back-end to the
production back-end, perhaps make a couple of changes for 'versioning', and
release it to your users. There's an article on versioning at
http://accdevel.tripod.com and Tony Toews' Auto FE Updater is downloadable
from his site, http://www.granite.ab.ca/accsmstr.htm.

In the less-common case where there are changes to the data structure,
probably in off-hours (the wee hours, the witching hour, etc., or on a
weekend when none of the users are working), you will have to modify and
change the data in the production back-end -- that will be sufficiently
unique that I can only advise that you should do it with no users in the DB.

I'm sure Doug assumed that you were sufficiently experienced here in the
newsgroups to have seen this advice repeated over and over by experienced
Access users, and that your DB was already split.

Larry Linson
Microsoft Office

 

[MVP - WannaB]

I'm not able to split the DB. That was the first thing I attempted, however
there are reports that
have many many subreports and subqueries, and when split these reports
produce the handles error.
So for the moment I am stuck working with 2 access DBs as they are
==================================================.

Thanks Doug. You say "can you not just replace the development
database with the production one". Did I imply that I was replacing the
development database with the production database, if so my mistake. What
I
think I can do and want to do is replace the data in the dev db with the
data in the production db. So as I am working in the dev db I have
current
data. I don't know of any reason that I shouldn't or couldn't do that, is
there one? Please elaborate.

You should have your database split into a front-end (queries, forms,
reports, macros, and modules) linked to tables in a back-end (tables with
data, and relationships). You will still have a dev copy (the one you are
changing) and a production copy (the last one released to the users) of the
front-end and a dev copy and production copy of the back end. The vast
majority of changes and enhancements to applications are only to the
front-end.

In this case, to "promote" your dev copy (after appropriate testing) to
production status, you simply relink it from the dev back-end to the
production back-end, perhaps make a couple of changes for 'versioning', and
release it to your users. There's an article on versioning at
http://accdevel.tripod.com and Tony Toews' Auto FE Updater is downloadable
from his site, http://www.granite.ab.ca/accsmstr.htm.

In the less-common case where there are changes to the data structure,
probably in off-hours (the wee hours, the witching hour, etc., or on a
weekend when none of the users are working), you will have to modify and
change the data in the production back-end -- that will be sufficiently
unique that I can only advise that you should do it with no users in the DB.

I'm sure Doug assumed that you were sufficiently experienced here in the
newsgroups to have seen this advice repeated over and over by experienced
Access users, and that your DB was already split.

Larry Linson
Microsoft Office

 

[Larry Linson]

I wish you well, then.

I've never dealt with a business problem so complex that it required the
data be in the same DB with the UI -- I've always been able to address the
busines problem at hand using good DB design practices.

Larry Linson
Microsoft Office Access MVP

 

[Douglas J. Steele]

The reason I'd give is the same one Larry's already given: your application
should be split into a front-end and back-end.

It's a recipe for disaster having multiple users sharing the same
application.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug. You say "can you not just replace the development
database with the production one". Did I imply that I was replacing the
development database with the production database, if so my mistake. What
I
think I can do and want to do is replace the data in the dev db with the
data in the production db. So as I am working in the dev db I have
current
data. I don't know of any reason that I shouldn't or couldn't do that, is
there one? Please elaborate.
=============================
You can refer to tables in other databases in queries without having to
link
the tables.

For instance, you could use

INSERT INTO MyTable (ID, Field1, Field2, Field3)
SELECT ID, Field1, Field2, Field3
FROM [;DATABASE=E:\Folder\File.mdb].MyTable
WHERE ID NOT IN (SELECT DISTINCT ID FROM MyTable)

You might also find my November, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access" to be relevant to what you're trying to do. I
show how to write a single query that will insert new rows and update
existing ones. You can download the column (and sample database) for free
from http://www.accessmvp.com/DJSteele/SmartAccess.html

That having been said, though, can you not just replace the development
database with the production one?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello, I have 2 nearly identical MDB files, one in production and the
copy
in dev. I would like to automate the process of pulling fresh data into
the
dev db and the only thing I can think of is to copy the Production db to
the
same folder as the dev db, create links to each table, and then build
update
and append queries to pull the data into the dev from the production. My
question is, How can this be done without all those extra objects, just
using VBA ??
Thank you.

 

[MVP - WannaB]

Thank you both for your time and views. Doug after rereading your initial
post I see that I mixed up a couple of words and misunderstood your
statement. When you wrote "can you not just replace the development database
with the production one?" I read that as "you CAN NOT just replace the
development database with the production one?" << I HATE WHEN I DO THAT
But now it all makes more sense...

In any event welcome to my world, I am by no means an MVP and sometimes I
don't even think I will ever get there. I have 2 access databases that were
put together by users who acquired some good help that they used to create a
mess which provided the users with most of what they needed. But I am now
charged with redeveloping these into one new application which will not be
using access. Over the last year I have done all that I could to improve the
existing applications to a point where they are now stable. I have a few
more requested features to add before I close the door on changes to the
current applications as they are. When I tried to split them I ran into many
problems with more then 4 reports that were so complicated that I felt it
better to leave them as is, and leave the DB as one, because it will all go
away when the new application is finished, this time next year.
=============================================

The reason I'd give is the same one Larry's already given: your application
should be split into a front-end and back-end.

It's a recipe for disaster having multiple users sharing the same
application.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug. You say "can you not just replace the development
database with the production one". Did I imply that I was replacing the
development database with the production database, if so my mistake. What
I
think I can do and want to do is replace the data in the dev db with the
data in the production db. So as I am working in the dev db I have
current
data. I don't know of any reason that I shouldn't or couldn't do that, is
there one? Please elaborate.
=============================
You can refer to tables in other databases in queries without having to
link
the tables.

For instance, you could use

INSERT INTO MyTable (ID, Field1, Field2, Field3)
SELECT ID, Field1, Field2, Field3
FROM [;DATABASE=E:\Folder\File.mdb].MyTable
WHERE ID NOT IN (SELECT DISTINCT ID FROM MyTable)

You might also find my November, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access" to be relevant to what you're trying to do. I
show how to write a single query that will insert new rows and update
existing ones. You can download the column (and sample database) for free
from http://www.accessmvp.com/DJSteele/SmartAccess.html

That having been said, though, can you not just replace the development
database with the production one?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello, I have 2 nearly identical MDB files, one in production and the
copy
in dev. I would like to automate the process of pulling fresh data into
the
dev db and the only thing I can think of is to copy the Production db to
the
same folder as the dev db, create links to each table, and then build
update
and append queries to pull the data into the dev from the production. My
question is, How can this be done without all those extra objects, just
using VBA ??
Thank you.

 

[David W. Fenton]

I'm not able to split the DB. That was the first thing I
attempted, however there are reports that
have many many subreports and subqueries, and when split these
reports produce the handles error.

Er, what? You mean the "too many databases" error that is an
indication of exceeding the 2048 limit on open table handles?

If so, that means your app is just really badly designed and needs
to be re-engineered.

In regard to splitting:

Every Access application with more than one user should be split. NO
EXCEPTIONS.

Every Access application where the user needs to get updates to the
forms/reports from someone else should be split, even if it has only
one user. NO EXCEPTIONS.

Every replicated Access app should be split. NO EXCEPTIONS.

And every user should have an individual copy of the front end, no
matter whether the app is running on a workstation or in a Terminal
Server/Citrix session. NO EXCEPTIONS.

 

[David W. Fenton]

When I tried to split them I ran into many
problems with more then 4 reports that were so complicated that I
felt it better to leave them as is, and leave the DB as one,
because it will all go away when the new application is finished,
this time next year.

Yes, and next year when it's not finished, it will be a year in the
future.

I see no reason why splitting should cause the slightest problems
with your reports, since a linked table is no different from the
report's point of view than a local table.

Tell us what the problems are with the report in the split version
and maybe we can come up with advice on how to fix it so that you
can solve the problem once and for all.

 

[MVP - WannaB]

Oh David you are openning up a big can of worms here. I spent too much time
trying to get this working last year, and posted many questions then and no
one was able to help me.
If you are sure you really want to get me going on this again and you
believe you will be able to help me get this working as a split db, I'm will
to go back there and try again.
=========================================

 

[Tom Wickerath]

Hi MVP - WannaB,

There are plenty of reasons to want to run a split application, not the
least of which is that sharing an entire application has been identified by
Microsoft personnel as one of the leading causes of JET database corruption.
(It was stated as the leading cause by a Microsoft Program Manager on the
Access Team, at a conference I attended several years ago--I'm not sure if it
still is the leading cause, or if it just remains as one of the top causes).

Split the Database
http://www.access.qbuilt.com/html/gem_tips1.html#SplitDB
http://www.accessmvp.com/JConrad/accessjunkie/splitting.html

It would be good if you could provide Google links to some (or all) of the
past threads you mentioned, so that we could bring ourselves up to speed
faster. Here is a search page link to get you started:

http://groups.google.com/advanced_s...r=&as_drrb=b&as_mind=1&as_minm=1&as_miny=2005

Use WannaB as the author.

Anywho, about the only issues that come to mind for local verses linked
tables is attempting to open a recordset using the optional parameter
dbOpenTable or attempting to use the .Seek method. These work with local
tables, but will fail with linked tables.

Cannot Perform OpenTable Method on Linked/Attached Table
http://support.microsoft.com/kb/208379

How to Use the Seek Method on Linked Tables
http://support.microsoft.com/kb/210266

Tables: How to perform Seek on Linked Tables
http://www.mvps.org/access/tables/tbl0006.htm

Regarding replacing a (linked) development back-end (BE) database with a
fresh copy of a (linked) production BE, Access MVP Armen Stein has just made
this process a whole lot easier by making his relinking code available for
free for the first time. You can get a copy from his web site:

J Street Access Relinker
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

You mentioned a new application slated to replace your application next
year. Just curious what product is being used to re-write the application,
and why you feel that it may be better than MS Access. It might be...I'm just
curious to know your thoughts on this.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[MVP - WannaB]

Hi Tom,
Thank you very much for all your advice, and these links, the Gem site looks
like a real GEM.

I'm away at training next week and may not have as much time to revisit this
issue during that time.

The new application will be .NET and SQL2005. The new application will do
all that the current application does PLUS what 2 other applications do and
more.

I have implemented Split DBs before and I've seen some relinking code, but
I'm not sure who it belonged to. I did not use it at that time, but only
studied it to learn a few things. If I do split this I would like to use the
relinking code, I remember it was exciting to read through and try to
understand how it was working, (it's like light bulbs turning on in my head).
While I see the need to split that database, I also need to balance out the
amount of time I spend on something that has been around for over 9 years, is
used by 8 people (maybe only 3 or 4 at the same time), is compact/repaired
weekly, is locked down with allowbypasskey=false, and has been very stable
for the last 8 months. Which was 4 months after I got here.

As for splitting this access db now I'm on a schedule and will need to think
this over for a few days then talk it over with the team and IF I do decide
to split, I will repost any posts with a SUBJECT line that starts with "SPLIT
– ". Does that sound reasonable?
Many Thanks again to all of you.
=====================================

 

[David W. Fenton]

The new application will be .NET and SQL2005. The new application
will do all that the current application does PLUS what 2 other
applications do and more.

How many times have professional Access developers heard this? Don't
believe it. It may or may not happen. I've seen big projects like
this abandoned at 90% complete, and there are plenty of reports of
just that kind of thing in the news (often big government projects,
in fact).

I have seldom seen one of my Access apps replaced by a non-Access
app that did not cost many multiples of what had already been
invested in the Access app, and lacked significant portions of the
features of the Access app. And usually slower. And usually hated by
the end users. And once abandoned after a time in favor of as return
to the Access app.

 

[David W. Fenton]

If I do split this I would like to use the
relinking code

I have distributed many apps with no relinking code in them. In
those cases, I manually relink to the production back end, and the
users just copy the front end with the updated links to their PCs.

Not sure why you're obsessing on relinking code. It's a non-issue.

And there are literally dozens of working samples available for
plugging into your app if you *do* need it. Mine is here:

http://dfenton.com/DFA/download/Access/Reconnect.html

That was specifically designed for relinking in apps with more than
one back end (all my apps have a temp database as well as the
production back end, so it's used in nearly all my production apps).