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 15 2378
"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
On Mon, 24 Jul 2006 17:34:38 GMT, "Larry Linson"
<bo*****@localhost.notwrote:
>"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
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 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
Shyguy wrote:
On Mon, 24 Jul 2006 17:34:38 GMT, "Larry Linson"
<bo*****@localhost.notwrote:
"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
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.
In article <be********************************@4ax.com>, sh****@aol.com
says...
On Mon, 24 Jul 2006 17:34:38 GMT, "Larry Linson"
<bo*****@localhost.notwrote:
"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
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.
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?
Mike Gramelspacher wrote:
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.
Mike, could you give us an overview - the basic idea/steps that are
tricky? Thanks for the code!
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?
On Mon, 24 Jul 2006 14:45:42 -0500, Mike Gramelspacher
<gr******@psci.netwrote:
>In article <be********************************@4ax.com>, sh****@aol.com says...
>On Mon, 24 Jul 2006 17:34:38 GMT, "Larry Linson" <bo*****@localhost.notwrote:
>"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
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?
Shyguy wrote:
On Mon, 24 Jul 2006 17:34:38 GMT, "Larry Linson"
<bo*****@localhost.notwrote:
"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
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.
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 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.
On 24 Jul 2006 17:56:40 -0700, "Lyle Fairfield"
<ly***********@aim.comwrote:
>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.
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.
Shyguy wrote:
On 24 Jul 2006 17:56:40 -0700, "Lyle Fairfield"
<ly***********@aim.comwrote:
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.
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.
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.
In article <11**********************@h48g2000cwc.googlegroups .com>, pi********@hotmail.com says...
>
Mike Gramelspacher wrote:
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.
Mike, could you give us an overview - the basic idea/steps that are
tricky? Thanks for the code!
Code posted here: http://www.psci.net/gramelsp/temp/Fu...CopyTables.txt
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"
<ly***********@aim.comwrote:
> Shyguy wrote:
>On 24 Jul 2006 17:56:40 -0700, "Lyle Fairfield" <ly***********@aim.comwrote:
>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.
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.
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Shawn Mehaffie |
last post by:
I have the following class that I've wirtten to take a Dataset and
automatically export it to either XML, ASCII or Tab delimited file. The
reason I wrote it they way I did was that I don't want to...
|
by: Bon |
last post by:
Hello All
I got "Tables are skipped or export failed" error when I used upsizing
wizard on migrating MS Access 2000 to MS SQL Server 2000.
I am changing my application backend database from MS...
|
by: Janne Ruuttunen |
last post by:
Hello DB2 people,
I'm having problems exporting >= 250000 lobs to IXF files with the
LOBSINFILE option, using a legacy DB2 2.1 system on Win NT.
If I don't specify a path for the lobs,...
|
by: DD |
last post by:
Hi
I have a split database, that is packaged and used in a runtime
enviroment.
I want to make it easier for the user, a button on the frontend
"Export Backend"
They can click on the button...
|
by: Shawn Mehaffie |
last post by:
I have the following class that I've wirtten to take a Dataset and automatically export it to either XML, ASCII or Tab delimited file. The reason I wrote it they way I did was that I don't want to...
|
by: Iavor Raytchev |
last post by:
Hello,
We a situation with a central database that contains the data that needs to be presented at N off-line terminals (N can be 5 000 can be 15 000). Each terminal presents unique data. The...
|
by: rdemyan via AccessMonster.com |
last post by:
My front-end code manually links to the backend file.
I was wondering what stops Access from linking to the system tables in the
backend file. Is it just by virtue that they are hidden?
This...
|
by: rdemyan via AccessMonster.com |
last post by:
I have a need to add another field to all of my tables (over 150). Not data,
but an actual field.
Can I code this somehow. So the code presumabley would loop through all the
tables, open each...
|
by: hemantbasva |
last post by:
Note We need to have a template on server for generating report in multiple sheet
as we do not had msoffice on server
moreover this require a batch job to delete excel file created by the...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |