Transfer Tables Directly to Backend Database

[JonMulder]

Greetings,

I've searched through the forums and haven't seen a definite "NO" to what I want to do.

I'd like to to import some large oracle tables from a remote server using "DoCmd.TransferDatabase..." on a daily basis. Currently, the system works, but my database is getting quite large (800 Mb) and I'd like to split off the tables to a back end database.

Is there a way to import directly to the back end database? The postings that I've seen seem to imply that one must first import the tables into my front end, then export them into the back end. Does that sound correct?

I'm at home right now, but will give it a try when I get to work. It just seems like there's got to be a more direct way!

Jon Mulder
Department of Water Resources
Red Bluff, CA

 

[DavidAtWork]

Is this other new back-end to be an access Db, if so you could have a windows scheduled task that would run daily and open this new BE- file and within it start a function that would import the Oracle tables.
Your could then have these BE tables as linked tables in your current database thereby keeping the size down.

David

 

[JonMulder]

I'm replacing the data everyday. We have Oracle tables for our statewide groundwater data in Sacramento, California (about 200 miles away). The dataset is rather huge with about 35,000 monitoring locations and about 1.5 million water level measurements going back to the early 1900's.

The data currently is accessed by staff in our building off the local server after I copy it over. I tried to link in to the Oracle tables directly and was successful. However, it took about 15 minutes to query out a set of measurements for a specific location as opposed to a few seconds when the data was copied to our local server.

We don't change or add to any of the data. It gets modified by local water districts / agencies / cooperators frequently when they measure their water levels in wells or sometimes add new monitoring wells.

We do have local tables (in addition to the statewide Oracle tables) that contain additional information such as well ownership and well construction information, both of which should have been incorporated into the web-based Oracle application, but weren't.

A few years ago, California passed legislation requiring the reporting of groundwater elevation measurement. The Department of Water Resources contracted out for a web-based application (CAlifornia Statewide Groundwater Elevation Monitoring [CASGEM]) for local agencies to submit their reports. Here's a link to the site: www.water.ca.gov/groundwater/casgem/

In my opinion, the system was poorly designed and functions very slowly. So, we've come up with a system with more robust search routines, reports that can export to Excel that contain the data WE need (not what the consultants thought we needed or prior staff told them we needed -- not nescessarily knocking the consultants; they did their job, got paid, and moved on leaving us a legacy program), additional statisical reports for groundwater analysis in ArcMap, and a slick Google Earth interface for management/other staff who don't have ArcMap on their machines.

Frankly, I'm a geologist, not a programmer, but I love working with the data and creating a system that is more user-friendly and addresses our needs.

I am definitely an advocate for keeping the statewide location and measurement infomation in one system rather than separate regional datasets in our four regional offices spread across the state. I started this post because I have a need to refresh the Oracle data sometimes daily after a staff member adds more well locations and then want to dump out location data (latitude, longitude, well type, etc) for producing a map.

I think I'm getting my problem solved regarding importing the Oracle tables into my Back End. I found some routines online that address refreshing Back End tables and creating indexes for quicker access on the fly (currently at home now so don't have the web address handy).

Hope that's not too much information <g>!

Jonathan Mulder
Engineering Geologist
California Department of Water Resources
Red Bluff, CA