Static tables

[Guest]

Dear Sirs
Is it possible to export table data to another access databse table.
The table should not be linked, that means - once the data is in the other
database table it should not be automatically updated, it should be static.
How can I do that?

 

[Dirk Goldgar]

Dear Sirs
Is it possible to export table data to another access databse table.
The table should not be linked, that means - once the data is in the
other database table it should not be automatically updated, it
should be static. How can I do that?

I'm not sure what you're after. Do you want to copy data to another
table in the same database? Or do you want to export it to a table in a
different database? Does the target table already exist? Does the
target database already exist? Do you want to copy all the records and
all the fields of each record from the source table, or only a subset of
records or fields? Is this something you want to do in an automated
way, using VBA code, or is it to be a manual operation performed via the
Access user interface?

 

[Guest]

Dear Dirk,

* I wish to export data into a table in a different database.
* I would like to export not all fields
* The target table already exist.
* I would like to do that automated with a transfer button

Im portant is that the target table keeps the information (even if I
transfer the whole target database onto my laptop and the source database is
gone)

 

[Dirk Goldgar]

Dear Dirk,

* I wish to export data into a table in a different database.
* I would like to export not all fields
* The target table already exist.
* I would like to do that automated with a transfer button

Im portant is that the target table keeps the information (even if I
transfer the whole target database onto my laptop and the source
database is gone)

Okay, let's assume that the name and path of the target database is
known to be "C:\Temp\Backup.mdb", and that you want to export the fields
ID and Description from all records in table "MyTable" (in the current
database) to fields BkpID and BkpDescription in table "BackupTable" in
the target database. Both the target database and the target table in
that database already exist.

You could use code like this behind your command button:

'------ start of example code ------
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Temp\Backup.mdb"
strTargetTable = "BackupTable"

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (BkpID, BkpDescription) IN '" & _
strTargetDB & _
"' SELECT MyTable.ID, MyTable.Description " & _
FROM MyTable;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'------ end of example code ------

That's air code, but something along those lines ought to work. Note
that the code also assumes that the target database is not protected by
a password or user-level security.

 

[Guest]

Dear Dirk
I tried this afternoon several ways to understand and get your code working
- I am now totally confused and nothing is working anymore.
Now I have the following:
***************************************
Private Sub cmdTransfer_Click()
On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transfer.mdb"
strTargetTable = "webtotalbalancebie30p"

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (initialinvestment, guaranteedequity, monthstartingbalance)
IN '" & _
strTargetDB & _
"' SELECT webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity,
webtotalbalancebie30p.monthstartingbalance " & _
FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
*********************************************
I always get the "Syntax error" message. Can you help to get this code
working. Maybe it is easier to copy the whole table to the back-up database
(I would prefer it by now)

Dear Dirk,

* I wish to export data into a table in a different database.
* I would like to export not all fields
* The target table already exist.
* I would like to do that automated with a transfer button

Im portant is that the target table keeps the information (even if I
transfer the whole target database onto my laptop and the source
database is gone)

Okay, let's assume that the name and path of the target database is
known to be "C:\Temp\Backup.mdb", and that you want to export the fields
ID and Description from all records in table "MyTable" (in the current
database) to fields BkpID and BkpDescription in table "BackupTable" in
the target database. Both the target database and the target table in
that database already exist.

You could use code like this behind your command button:

'------ start of example code ------
Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Temp\Backup.mdb"
strTargetTable = "BackupTable"

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (BkpID, BkpDescription) IN '" & _
strTargetDB & _
"' SELECT MyTable.ID, MyTable.Description " & _
FROM MyTable;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'------ end of example code ------

That's air code, but something along those lines ought to work. Note
that the code also assumes that the target database is not protected by
a password or user-level security.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dear Dirk
I tried this afternoon several ways to understand and get your code
working - I am now totally confused and nothing is working anymore.
Now I have the following:
***************************************
Private Sub cmdTransfer_Click()
On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transfer.mdb"
strTargetTable = "webtotalbalancebie30p"

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (initialinvestment, guaranteedequity,
monthstartingbalance) IN '" & _
strTargetDB & _
"' SELECT webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity,
webtotalbalancebie30p.monthstartingbalance " & _
FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
*********************************************
I always get the "Syntax error" message. Can you help to get this code
working. Maybe it is easier to copy the whole table to the back-up
database (I would prefer it by now)

I'm sorry, I left off a quote in my example code, rendering the whole
statement invalid. In your code, change this line:

FROM webtotalbalancebie30p;"

to this:

"FROM webtotalbalancebie30p;"

Assuming you've got the rest of the field and table names changed
correctly, that ought to work. I just tested it, and it worked for me.

 

[Guest]

Hi Dirk

One last question:
How does the code look if I would like to copy the whole table?
Thanks
Klaus

Dear Dirk
I tried this afternoon several ways to understand and get your code
working - I am now totally confused and nothing is working anymore.
Now I have the following:
***************************************
Private Sub cmdTransfer_Click()
On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transfer.mdb"
strTargetTable = "webtotalbalancebie30p"

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (initialinvestment, guaranteedequity,
monthstartingbalance) IN '" & _
strTargetDB & _
"' SELECT webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity,
webtotalbalancebie30p.monthstartingbalance " & _
FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
*********************************************
I always get the "Syntax error" message. Can you help to get this code
working. Maybe it is easier to copy the whole table to the back-up
database (I would prefer it by now)

I'm sorry, I left off a quote in my example code, rendering the whole
statement invalid. In your code, change this line:

FROM webtotalbalancebie30p;"

to this:

"FROM webtotalbalancebie30p;"

Assuming you've got the rest of the field and table names changed
correctly, that ought to work. I just tested it, and it worked for me.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk

One last question:
How does the code look if I would like to copy the whole table?
Thanks
Klaus

That code is copying all the records from the table this to the
(existing) table in the other database. If you want to copy the whole
table, creating a new table in the target DB, there are a couple of ways
to do it.

You could use DoCmd.CopyObject:

DoCmd.CopyObject _
strTargetDB, _
strTargetTable, _
acTable, _
strSourceTable

Or you could use TransferDatabase:

DoCmd.TransferDatabase _
acExport, _
"Microsoft Access", _
strTargetDB, _
acTable, _
strSourceTable, _
strTargetTable

There are other ways, as well, but these are probably the simplest. I
can't remember at the moment what happens if the target table already
exists -- I'm not sure if it overwrites it, or just fails.

 

[Guest]

Dirk thanks,
you wrote "I'm not sure if it overwrites it..."
Actually I would like that it overrides it because I only would like to have
the last record in the table.
I tried it but it adds on - how can I override the table?

 

[Dirk Goldgar]

Dirk thanks,
you wrote "I'm not sure if it overwrites it..."
Actually I would like that it overrides it because I only would like
to have the last record in the table.
I tried it but it adds on - how can I override the table?

You may have to delete the table first, and then copy the table. Code
to delete the table would be something like this:

Dim dbTarget As DAO.Database

Set dbTarget = DBEngine.OpenDatabase(strTargetDB)

On Error Resume Next ' ignore error if the table doesn't exist
dbTarget.TableDefs.Delete strTargetTable
On Error GoTo 0 ' or go to your error-handler

dbTarget.Close
Set dbTarget = Nothing

 

[Guest]

Sorry Dirk, but I have no idea what to write where, in your delete code.
and - can I delete a table in the target database from the source database?
Is it possible for you to write the code so that I can see where I have to
input my lines and which?
Many thanks
Klaus

 

[Dirk Goldgar]

Dirk thanks,
you wrote "I'm not sure if it overwrites it..."
Actually I would like that it overrides it because I only would like
to have the last record in the table.
I tried it but it adds on - how can I override the table?

Better than deleting and recreating the table might be to simply empty
it each time. You could adapt the code used for the append query, and
run first a delete query and then the append query:

With CurrentDb

strSQL = _
"DELETE FROM [" & strTargetTable & "] " & _
"IN '" & _
strTargetDB & "';"

.Execute strSQL, dbFailOnError

strSQL = _
"INSERT INTO [" & strTargetTable & "] " & _
"(initialinvestment, guaranteedequity, " & _
"monthstartingbalance) " & _
"IN '" & _
strTargetDB & _
"' SELECT initialinvestment, guaranteedequity, " & _
"monthstartingbalance " & _
"FROM webtotalbalancebie30p;"

.Execute strSQL, dbFailOnError

End With

 

[Dirk Goldgar]

Sorry Dirk, but I have no idea what to write where, in your delete
code. and - can I delete a table in the target database from the
source database? Is it possible for you to write the code so that I
can see where I have to input my lines and which?

It would go right before the code to copy the table. However, I like my
second idea better; the one I just posted in a separate followup. See
if that looks good to you.

If not, post back, but I'm going offline now, so I won't have an answer
until at least several hours from now.

 

[Guest]

Dear Dirk
Now nothing is working anymore.
I believe I am totally wrong.
No I tried to adapt your code with the existing one but I think that's not
right - but - what do I have to do?
Now it looks like this (it's totally wrong)


Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb"
strTargetTable = "webtotalbalancebie30p"

With CurrentDb

strSQL = _
"DELETE FROM [" & strTargetTable & "] " & _
"IN '" & _
strTargetDB & "';"

.Execute strSQL, dbFailOnError

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity, ) IN '"
& _
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity " & _
"FROM webtotalbalancebie30p;"


CurrentDb.Execute strSQL, dbFailOnError

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub

Dirk thanks,
you wrote "I'm not sure if it overwrites it..."
Actually I would like that it overrides it because I only would like
to have the last record in the table.
I tried it but it adds on - how can I override the table?

Better than deleting and recreating the table might be to simply empty
it each time. You could adapt the code used for the append query, and
run first a delete query and then the append query:

With CurrentDb

strSQL = _
"DELETE FROM [" & strTargetTable & "] " & _
"IN '" & _
strTargetDB & "';"

.Execute strSQL, dbFailOnError

strSQL = _
"INSERT INTO [" & strTargetTable & "] " & _
"(initialinvestment, guaranteedequity, " & _
"monthstartingbalance) " & _
"IN '" & _
strTargetDB & _
"' SELECT initialinvestment, guaranteedequity, " & _
"monthstartingbalance " & _
"FROM webtotalbalancebie30p;"

.Execute strSQL, dbFailOnError

End With


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dear Dirk
Now nothing is working anymore.

Ah, I see we're making progress! <g>

Seriously, you have to tell us what you mean when you say "nothing is
working". What happens? Do you get an error message? If so, what
exactly is the message (and number, if available)? Without that
information, we can only guess what may be wrong. We're not
clairvoyant.

That said, I believe I see an error in the code you posted, though it
mostly looks okay to me, not "totally wrong". But this line:

"] (accountnumber, initialinvestment, guaranteedequity, ) IN '" &

_

.... has a syntax error. There should be no trailing comma within the
parentheses. That should be:

"] (accountnumber, initialinvestment, guaranteedequity) IN '" & _

Fix that, and then tell me whether it works or not. If it doesn't work,
please give a full description of the error.

 

[Guest]

Hello Dirk,
Sorry for yesterday, I was sitting the whole day infront of the program, got
tired, unconcentrated and made silly mistakes and therefore I asked silly
questions.

Now, your code is working and if I push the button, it's doing what he
should do.
I realized that I have to do the same procedure with another table. Is it
possible to run all the commands with one button click?
If yes, what and where do I have to insert the code?

Kindest regrads
Klaus

Dear Dirk
Now nothing is working anymore.

Ah, I see we're making progress! <g>

Seriously, you have to tell us what you mean when you say "nothing is
working". What happens? Do you get an error message? If so, what
exactly is the message (and number, if available)? Without that
information, we can only guess what may be wrong. We're not
clairvoyant.

That said, I believe I see an error in the code you posted, though it
mostly looks okay to me, not "totally wrong". But this line:

"] (accountnumber, initialinvestment, guaranteedequity, ) IN '" &

_

.... has a syntax error. There should be no trailing comma within the
parentheses. That should be:

"] (accountnumber, initialinvestment, guaranteedequity) IN '" & _

Fix that, and then tell me whether it works or not. If it doesn't work,
please give a full description of the error.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Dear Dirk
This is a continuation of my last mail where I tried to combine to equal
commands for to different tables in one command button.
Here you will find the code I tried to write.
*At first I got the error message: Compile error, Duplicate declaration in
current scope. - I took the duplicate out
* than I got the next error message: Syntax error in INSERT INTO statement.
Please have a look at the code and maybe you can tell what I am doing wrong.
Many thanks in advance
Klaus
**************************CODE**************************

Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

Dim strTargetDB As String
Dim strTargetTable As String
Dim strSQL As String

strTargetDB = "C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb"
strTargetTable = "webtotalbalancebie30p"

With CurrentDb

strSQL = _
"DELETE FROM [" & strTargetTable & "] " & _
"IN '" & _
strTargetDB & "';"

.Execute strSQL, dbFailOnError

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (accountnumber, initialinvestment, guaranteedequity,
monthstartingbalance, SumOfcontractvalue, commission, managementfeebasic,
Incentivefeetotal, electronicfee, vat, adjustment, netliquidationbalance,
SumOfopenpl, SumOfinitialmargin, SumOfmaintenancemargin, opentradebalance) IN
'" & _
strTargetDB & _
"' SELECT webtotalbalancebie30p.accountnumber,
webtotalbalancebie30p.initialinvestment,
webtotalbalancebie30p.guaranteedequity,
webtotalbalancebie30p.monthstartingbalance,
webtotalbalancebie30p.SumOfcontractvalue, webtotalbalancebie30p.commission,
webtotalbalancebie30p.managementfeebasic,
webtotalbalancebie30p.incentivefeetotal, webtotalbalancebie30p.electronicfee,
webtotalbalancebie30p.vat, webtotalbalancebie30p.adjustment,
webtotalbalancebie30p.netliquidationbalance,
webtotalbalancebie30p.SumOfopenpl, webtotalbalancebie30p.SumOfinitialmargin,
webtotalbalancebie30p.SumOfmaintenancemargin,
webtotalbalancebie30p.opentradebalance " & _
"FROM webtotalbalancebie30p;"

CurrentDb.Execute strSQL, dbFailOnError

End With

strTargetDB = "C:\Documents and Settings\Klaus
Müller\Desktop\cps207\transferdb.mdb"
strTargetTable = "weboffsetordersbie30p"

With CurrentDb

strSQL = _
"DELETE FROM [" & strTargetTable & "] " & _
"IN '" & _
strTargetDB & "';"

.Execute strSQL, dbFailOnError

strSQL = _
"INSERT INTO [" & strTargetTable & _
"] (tradeid, clearingnumber, date, bought, sold, commodity,
month, year, price, contractvalue) IN '" & _
strTargetDB & _
"' SELECT weboffsetordersbie30p.tradeid,
weboffsetordersbie30p.clearingnumber, weboffsetordersbie30p.date,
weboffsetordersbie30p.bought, weboffsetordersbie30p.sold,
weboffsetordersbie30p.commodity, weboffsetordersbie30p.month,
weboffsetordersbie30p.year, weboffsetordersbie30p.price,
weboffsetordersbie30p.contractvalue " & _
"FROM weboffsetordersbie30p;"

CurrentDb.Execute strSQL, dbFailOnError

End With

Exit_Point:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
****************************************************

Dear Dirk
Now nothing is working anymore.

Ah, I see we're making progress! <g>

Seriously, you have to tell us what you mean when you say "nothing is
working". What happens? Do you get an error message? If so, what
exactly is the message (and number, if available)? Without that
information, we can only guess what may be wrong. We're not
clairvoyant.

That said, I believe I see an error in the code you posted, though it
mostly looks okay to me, not "totally wrong". But this line:

"] (accountnumber, initialinvestment, guaranteedequity, ) IN '" &

_

.... has a syntax error. There should be no trailing comma within the
parentheses. That should be:

"] (accountnumber, initialinvestment, guaranteedequity) IN '" & _

Fix that, and then tell me whether it works or not. If it doesn't work,
please give a full description of the error.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)