Export tables from Backend

[Shyguy]

I am trying to export 3 tables from the backend of the database while
working in the front end. Is this possible? And, if so, how?

Thank you for any help

[Larry Linson]

"Shyguy" wrote

> I am trying to export 3 tables from
> the backend of the database while
> working in the front end. Is this
> possible? And, if so, how?

Export to _what_? From the UI? From VBA code?

Larry Linson
Microsoft Access MVP

[Shyguy]

Sorry for not giving enough info.

I want to be able to click a button on the front end (UI?) and (I'm
not sure about this) run code on the backend (the data) to export 3
tables to a different db.

I I tried doing it from the Front End (UI) but all it did was export
the links to the tables in the backend.

Thanks for any help.

[ManningFan]

I believe my expertise is needed here...

Just add this to the back of your code:

DoCmd.TransferDatabase acExport, "Microsoft Access", LFilename,
acTable, "DataEntry Table1", "DataEntry Table1", False

The above should be on one line. If it's on 2 lines, condense them
into one.

Peyton Manning
Microsoft Access MVP

[Mike Gramelspacher]

In article <be3ac2lqjk5dpfppb...@4ax.com>, shy...@aol.com
says...

> On Mon, 24 Jul 2006 17:34:38 GMT, "Larry Linson"
> <bou...@localhost.not> wrote:
>
> >"Shyguy" wrote
> >
> > > I am trying to export 3 tables from
> > > the backend of the database while

> Sorry for not giving enough info.

>
> I want to be able to click a button on the front end (UI?) and (I'm
> not sure about this) run code on the backend (the data) to export 3
> tables to a different db.
>
> I I tried doing it from the Front End (UI) but all it did was export
> the links to the tables in the backend.
>
> Thanks for any help.
>

I do not know who originally post this, but not I. I have code to do
this in a database I use. see last form image here:
http://www.psci.net/gramelsp/temp/ChurchTemplate.html

' beginning of quoted part of message
Just as an exercise, I ran a test - worked fine:

Sub threeDBcopy()
Dim objAcc As Access.Application
Dim DB2_Name$, tableInDB2$
Dim DB3_Name$, tableInDB3$
Set objAcc = CreateObject("Access.Application.9")
DB2_Name$ = "F:\DbData2000\xxxx.mdb"
DB3_Name$ = "F:\DbData2000\yyyy.mdb"
tableInDB2$ = "Works Order"
tableInDB3$ = "Works Order"
objAcc.OpenCurrentDatabase DB2_Name$
objAcc.DoCmd.TransferDatabase acExport, "Microsoft Access", DB3_Name
$,
acTable, tableInDB2$, tableInDB3$
objAcc.Quit
Set objAcc = Nothing
End Sub

Copies a table from database #2 to database #3
from code in database #1

[Mike Gramelspacher]

I should add to what I previously wrote that I could send my code from
the church database program. It is 130 lines and that is too much for
here I think.

[pietl...@hotmail.com]

> I do not know who originally post this, but not I. I have code to do
> this in a database I use. see last form image here:
> http://www.psci.net/gramelsp/temp/ChurchTemplate.html
>
> ' beginning of quoted part of message
> Just as an exercise, I ran a test - worked fine:
>
> Sub threeDBcopy()
> Dim objAcc As Access.Application
> Dim DB2_Name$, tableInDB2$
> Dim DB3_Name$, tableInDB3$
> Set objAcc = CreateObject("Access.Application.9")
> DB2_Name$ = "F:\DbData2000\xxxx.mdb"
> DB3_Name$ = "F:\DbData2000\yyyy.mdb"
> tableInDB2$ = "Works Order"
> tableInDB3$ = "Works Order"
> objAcc.OpenCurrentDatabase DB2_Name$
> objAcc.DoCmd.TransferDatabase acExport, "Microsoft Access", DB3_Name
> $,
> acTable, tableInDB2$, tableInDB3$
> objAcc.Quit
> Set objAcc = Nothing
> End Sub
>
> Copies a table from database #2 to database #3
> from code in database #1

Oh cool! Wait Mike, you forgot to swagger and brag about how utterly
_brilliant_ you are. Wait... you can't do that when you borrow the
code. did ManningFan write it for you?

[pietl...@hotmail.com]

Mike, could you give us an overview - the basic idea/steps that are
tricky? Thanks for the code!

[Lyle Fairfield]

Mike Gramelspacher wrote:
> I do not know who originally post this, but not I. I have code to do
> this in a database I use. see last form image here:
> http://www.psci.net/gramelsp/temp/ChurchTemplate.html
>
> ' beginning of quoted part of message
> Just as an exercise, I ran a test - worked fine:
>
> Sub threeDBcopy()
> Dim objAcc As Access.Application
> Dim DB2_Name$, tableInDB2$
> Dim DB3_Name$, tableInDB3$
> Set objAcc = CreateObject("Access.Application.9")
> DB2_Name$ = "F:\DbData2000\xxxx.mdb"
> DB3_Name$ = "F:\DbData2000\yyyy.mdb"
> tableInDB2$ = "Works Order"
> tableInDB3$ = "Works Order"
> objAcc.OpenCurrentDatabase DB2_Name$
> objAcc.DoCmd.TransferDatabase acExport, "Microsoft Access", DB3_Name
> $,
> acTable, tableInDB2$, tableInDB3$
> objAcc.Quit
> Set objAcc = Nothing
> End Sub
>
> Copies a table from database #2 to database #3
> from code in database #1

I've never seen anything so obscenely inefficient and bizarre
previously. Can we assume that God helped on this one?

[Shyguy]

Thanks for the reply and code. When I try it I get the following.

Rin-time error '429':

ActiveX component can't create object

I renamed the Dim DB2_Name$'s and tableInDB2$'s. Am I supposed to
replace the "Access.Application.9" with something?

[Lyle Fairfield]

Assuming that you know (if you don't then come back) the full paths of
the backend and the "different" db then.

CurrentDb().Execute "SELECT * " _
& "INTO " _
& "[C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\Downloads.mdb].[Employees] " _
& "FROM " _
& "[C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\Northwind.mdb].[Employees]"

for each table; this one is for Employees.
News clients will insert extraneous line breaks. You need to remove
them.

Now, do you need indexes? Are there indexes? Indexes are a different
matter.

[Lyle Fairfield]

Lyle Fairfield wrote:
> Assuming that you know (if you don't then come back) the full paths of
> the backend and the "different" db then.
>
> CurrentDb().Execute "SELECT * " _
> & "INTO " _
> & "[C:\Documents and Settings\Lyle Fairfield\My
> Documents\Access\Downloads.mdb].[Employees] " _
> & "FROM " _
> & "[C:\Documents and Settings\Lyle Fairfield\My
> Documents\Access\Northwind.mdb].[Employees]"
>
> for each table; this one is for Employees.
> News clients will insert extraneous line breaks. You need to remove
> them.
>
> Now, do you need indexes? Are there indexes? Indexes are a different
> matter.

Of course, this is nonsense. Unless we are doing some kind of update of
legacy data we should never have to copy or recreate a table. Once the
data is saved it's saved. Duplication (except in the context of backup)
is the height of inefficiency and it indicates very poor design.
You might wonder why experienced Developers have not yet responded with
an efficient method. It's because experienced Developers don't do what
you are asking.

[Shyguy]

On 24 Jul 2006 17:56:40 -0700, "Lyle Fairfield"
<lylefa...@aim.com> wrote:

I am going to use this for backup purposes. Rather than backup the
entire database I am going to try to just backup the 2 or 3 critical
tables.

If you are trying to insinuate that I am not an experienced developer
than you are absolutely correct! ;-)

That said, if this method is terribly inneficient I would gladly
accept a method to backup the entire backend from the front end. I
probobly didn't mention that the front end and back end are are
different computers, but I don't think that really matters.

[Lyle Fairfield]

Probably if you are just backing up the data you don't need indexes
which means that the SQL I posted previously is adequate.

Here are some things you might try:

Sub temp()

CurrentDb().Execute "SELECT * " _
& "INTO " _
& "[C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\Downloads.mdb].[Employees] " _
& "FROM " _
& "[C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\Northwind.mdb].[Employees]"

End Sub

Usually can happen unless someone has Employees Table locked.

********
Sub temp2()
DBEngine.CompactDatabase _

"C:\Documents and Settings\Lyle Fairfield\My

Documents\Access\Northwind.mdb", _

"C:\Documents and Settings\Lyle Fairfield\My

Documents\Access\Northwind" & Format(Now(), "yyyymmddnnmmss") & ".mdb"
End Sub

Can happen if the db can be opened exclusively, see below.
**********
Sub temp3()
Dim a As Access.Application
Set a = New Access.Application
With a
.OpenCurrentDatabase "C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\Northwind.mdb"
.SaveAsText 6, "", "C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\Northwind" & Format(Now(), "yyyymmddnnmmss") & ".mdb"
.Quit
End With
Set a = Nothing
End Sub

Can happen if the db can be opened exclusively, see below. More often
than not, many times more efficient than temp2. temp2 applied to
results in a db of
1864 kb, temp3 in a db of 1036 kb. May not work if you have special
permissions set on the be.
********
Sub temp4()
Debug.Print CurrentProject.Connection.Execute("SELECT Distinct
CStr(DataBase) AS db" _
& " FROM MSysObjects WHERE Type=6").Collect(0)
End Sub

prints in the immediate window the path to your backend ... assumes you
have only one access be.
******

Private Function CanBeOpenedExclusively(ByVal FullPath As String) As
Boolean
Dim d As Database
Dim p As PrivDBEngine
Set p = New PrivDBEngine
On Error Resume Next
Set d = p(0).OpenDatabase(FullPath, True)
CanBeOpenedExclusively = Not (d Is Nothing)
p(0).Close
Set d = Nothing
Set p = Nothing
End Function

tells you if you can compact the be -> fullpath. This is all you need
to be able to compact. It is not necessary that everyone be logged out.

[Mike Gramelspacher]

In article <1153770973.4...@h48g2000cwc.googlegroups.com>,
pietl...@hotmail.com says...

Code posted here:
http://www.psci.net/gramelsp/temp/FunctionCopyTables.txt

[Shyguy]

Thank you for your help and code. The first sql you sent worked fine
for my need. Thank you also for the other code you sent. It will
take me a bit to obsorb all of it. ;-)

Thank you again.

On 24 Jul 2006 20:56:05 -0700, "Lyle Fairfield"