Transfer of data - CONTINUATION - maybe easier

[Amateur]

Maybe this is easier as my earlier post.
I have a Form called “Mainswitchboard†as a shortcut on my desktop which is
opening, with a command button, a Form called “trading†from another
database.
On the Form “trading†is a command button which should transfer data from
the trading DB to another database.
The target string is correct and the table, where the data has to be
transferred to, as well.
I always get the error message “3078 The MS Jet Database engine cannot find
the input table ….†. – (but that is all correct I checked it several times)
Can someone tell me, why I get this message and how I can correct it?
Thanks
Klaus

 

[Tom van Stiphout]

On Thu, 31 Jan 2008 05:41:01 -0800, Amateur

If you want to transfer data from a table in one database to a table
in another db, perhaps an Append query would be the simplest way: the
other table is attached to your main db, and you simply create an
Append query to insert into it.
-Tom.

 

[Amateur]

Tom, thanks for your reply - but I have to run a huge lot of codes to
transfer several tables - PLEASE be as kind as to look on my original Post,
3lines under this one. Maybe you can help then easier.
Thanks
Klaus

 

[Wayne-I-M]

Hi

Just an idea (as advised by Tom) you could use an append query to do all
this very simply. Something like this

You create the append query source table each time the code is run so you
don't have duplicate data. The append will please thedata into your tables.

As you see I like to keep things simple. This method would work fine for
your OnClick but you have not given the paths so I can't insert this (from
source)

In your other post you say

With this shortcut I can navigate through my different DB’s.

So I would also dim the variable as the "Path". I assume you have it/them
somewhere on your form. This way you can use the same imput form for any DB
you can reach. You will need to have the path for each DB "somewhere" -
again keeping it simple just have the path as a 0cm column in a combo.
Select the combo row andclick. This way you can import for anywhere.


Private Sub cmdTransfer_Click()
Dim ImportIMPfile As TableDef
For Each ImportIMPfile In CurrentDb.TableDefs
If ImportIMPfile.Name = "Your NEW append query source TABLE name" Then
CurrentDb.TableDefs.Delete ImportIMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"Path to the SOURCE DB.mdb", acTable, " The SOURCE table name", _
" C:\Documents and Settings\KlausMüller\Desktop\cps208web\transferdb.mdb"
DoCmd.SetWarnings False _
DoCmd.OpenQuery " Your NEW append query "
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "Your NEW append query source TABLE"
End Sub

 

[Amateur]

Dear Wayne thanks for your response - BUT -
If possible i would like to leave my code as it is because, in the day to
day business I can easier change the code as the query.
If you look at the (part)code from my Post "Transfer of Database" and my
last paragraph, you'll understand my problem better.
If I open the DB "Trading" directly with the Form"daily trading" with the
command button "Transfer" - everything is running perfect.
If I open the Form "daily trading" from the desktop shortcut Form
"Mainswitchboard" I get the error message.
That means, normally the code is correct - I just forgot something (what, I
don't know).
I think it has something to do with opening the "Trading" Form from the
desktop shortcut Form "Mainswitchboard".
Any idea for that?
Thanks
Klaus

:

 

[Wayne-I-M]

I can't see where your shortcut leads to so I would check the path using the
main run command - just in case that is the problem. You can right click the
short cut and cut the path. Paste it into run and see where it leads. If it
leads no-where this is your problem

 

[Amateur]

Puhh, I think I can not explain it good enough - but I will try now.
I have the following database in c:\cps208:
-Mainswitchboard
-Sales
-Statements
-Billing
-Trading

In the "Mainswitchboard" DB is a form called "Navigation".
From this Form "Navigation" I created a shortcut to my desktop.
From this "Navigation" Form, I can open, with the use of command buttons,
the main Forms from each database.
-If I use the command button "Trading", the main Form "daily trading" opens
and I can work with it (all functions are working perfect - except this
transferring thing).
This only happens if I open the Form "daily trading" from the desktop
"Navigation" Form.
-If I open the "Trading" DB with the "daily trading" Form directly from
c:\cps208 my transfer code is working.

As I said before, I think my "daily Trading" Form (with the Transfer command
button) is not working because I open it (only the form and not the DB
"Trading") from the "Navigation Form desktop shortcut.

It really makes me ...... ( I do not have the correct word for it) because I
am sitting already 5 days on this.

Please, any help is very much appreciated.
Thanks
Klaus

 

[Wayne-I-M]

I still think the problem lays with the path - although I may be wrong.

It may be the as your path (from Trading) is to a DB link twith the access
programme running but our path (from daily Trading) may not include the
access exe

Give it a try You need to load the "full" path
Something like this
C:\Program Files\Microsoft Office\OFFICE11 _
\MSACCESS.EXE"" ""C:\FolderName\DateBaseName.mdb

 

[Amateur]

Hi Wayne,
I did now the following and I receive the "Error message 3055 Not valid file
name".
Did I misunderstood you?

Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

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

strTargetDB = "C:\Program Files\Microsoft Office\OFFICE11
_\MSACCESS.EXE"" ""C:\Documents and Settings\Klaus
Müller\Desktop\cps208web\transferdb.mdb"
strTargetTable = "webtotalbalancebie30p"

With CurrentDb

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

.Execute strSQL, dbFailOnError

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


CurrentDb.Execute strSQL, dbFailOnError

End With

 

[Douglas J. Steele]

You don't need the reference to msaccess.exe in strTargetDB. For how you're
using it (in the IN clause), all you want is the full path to the mdb.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Wayne,
I did now the following and I receive the "Error message 3055 Not valid
file
name".
Did I misunderstood you?

Private Sub cmdTransfer_Click()

On Error GoTo Err_Handler

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

strTargetDB = "C:\Program Files\Microsoft Office\OFFICE11
_\MSACCESS.EXE"" ""C:\Documents and Settings\Klaus
Müller\Desktop\cps208web\transferdb.mdb"
strTargetTable = "webtotalbalancebie30p"

With CurrentDb

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

.Execute strSQL, dbFailOnError

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


CurrentDb.Execute strSQL, dbFailOnError

End With

I still think the problem lays with the path - although I may be wrong.

It may be the as your path (from Trading) is to a DB link twith the
access
programme running but our path (from daily Trading) may not include the
access exe

Give it a try You need to load the "full" path
Something like this
C:\Program Files\Microsoft Office\OFFICE11 _
\MSACCESS.EXE"" ""C:\FolderName\DateBaseName.mdb

 

[Amateur]

Wayne - problem solved.
Instead of opening the Form from my shortcut - I open now the database and
let the Form Pop-up when opening.
It works.
Thanks for your help.
Klaus