Import Tables Directly to Back End 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

[June7]

You need to import new tables daily, not just the records into existing tables?

Might be able to use SELECT INTO sql action. The basic syntax is:

SELECT * INTO table IN path/database FROM table;

The question is if the FROM clause can reference another external data source. Indications are it is possible:
http://forums.databasejournal.com/sh...rom-another-db
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=72331

[JonMulder]

June7,

Thanks for the reply! I've been muddling through this for the past day. Here's what I want to do.

Create Back End database of tables (do this only once).

Refresh the Back End Tables on a regular (daily or every few days).

In searching the forums some more, I came across a post about copying tables from DatabaseA (Oracle tables on a remote server) into DatabaseB(my Back End tables on a local server with VBA code in DatabaseC (my Front End application).

So I tried the following:
Sub TryAgainGetCASGEMTable(strODBCConnect, strTableName)
Dim strBackEndDatabase As String
strBackEndDatabase = GetDBPath() & Left(CurrentProject.Name, Len(CurrentProject.Name) - 18) & "_Tables.accdb"
Dim dbsBackEndDatabase As Database
Set dbsBackEndDatabase = OpenDatabase(strBackEndDatabase)
DoCmd.TransferDatabase acImport, "ODBC", strODBCConnect, acTable, strTableName, strTableName, False
dbsBackEndDatabase.Close
Set dbsBackEndDatabase = Nothing
End Sub


That procedure opens up my Back End Database and DOES copy tables over from the Oracle server, so that part works.

BUT, when I look at my tables in my Front End, they are no longer "linked", but actually in the Front End.

QUESTION: Is there something more I need to do to tell my Front End that the tables are linked and not imported?

Thanks for any help you can provide!

Jon Mulder
Engineering Geologist
Red Bluff, California

[June7]

I don't see the backend path referenced in the TransferDatabase arguments. Just opening db as an object in VBA doesn't mean it will automatically be targeted.

Review this recent thread with similar issue
https://www.accessforums.net/program...ext-33247.html