Docmd.Transferdatabase error

E

eggpap

Hello,

In a workbook I need to import tables from one mdb password protected
database to another mdb unprotected database. I though to use the
following subroutine:

Sub Export(dbname As String, tbSource As String, tbDestination As
String)
Dim dbSourcePath As String, dbTargetPath As String, oApp As
Access.Application
On Error GoTo err_hnd
'Path dbname
Application.AutomationSecurity = msoAutomationSecurityLow
dbSourcePath = ThisWorkbook.Path & "\" & dbname
dbTargetPath = ThisWorkbook.Path & "\" & "ImportExport.mdb"
'Apre Access
Set oApp = CreateObject("Access.Application")
oApp.Visible = False
'Apre nomedb
oApp.OpenCurrentDatabase dbSourcePath, , PWORD
oApp.DoCmd.TransferDatabase acExport, "MS Access", dbTargetPath,
acTable, tbSource, tbDestination
oApp.Quit
Set oApp = Nothing
Exit Sub
err_hnd:
MsgBox Err.Description & "/" & Err.Number & " Sub Export"
Resume Next
End Sub

However I get the 2507 error on the docmd.transferdatabase statement
that says "MS Access database type not installed or task not allowed"

Any tip?
Thanks
 
J

joel

Your parameter don't seem correct. If you skip some paraters then you must
include the parameter name usin ":=". I also thing if you are need a file
name not a Path. I think you want to concatenate the path with the filename.


oApp.DoCmd.TransferDatabase _
TransferType:=acExport, _
DatabaseName:="MS Access", _
dbTargetPath, _
acTable, _
tbSource, _
tbDestination

expression.TransferDatabase( _
TransferType, _
DatabaseType, _
DatabaseName, _
ObjectType, _
Source, _
Destination, _
StructureOnly, _
StoreLogin)
 
D

Dave Patrick

You can't use named arguments in this manner. You didn't provide a value for
TransferType. Try substituting a 1 for the argument 'acExport'


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
 
J

joel

Dave: You are wrong. If you have the Access Referernce library selected
excel will recognize this parameter. OPenCurrent Database is not producing
an error so the arguments are being recognized.

Dave Patrick said:
You can't use named arguments in this manner. You didn't provide a value for
TransferType. Try substituting a 1 for the argument 'acExport'


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
Click to expand...
 
D

Dave Peterson

I don't think so.

This is the line that's important:
Dim oApp As Access.Application
(removed the first few variables)

means early binding.

I wouldn't use this line with that declaration, but it's not important.
Set oApp = CreateObject("Access.Application")

Dave said:
The OP is using late binding so I assumed there was no reference.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

joel said:
Dave: You are wrong. If you have the Access Referernce library selected
excel will recognize this parameter. OPenCurrent Database is not
producing
an error so the arguments are being recognized.
Click to expand...
Click to expand...
 
D

Dave Patrick

Yes, I missed that but isn't the last assignment for oApp what's important?


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
 
E

eggpap

joel;297512 said:
Your parameter don't seem correct. If you skip some paraters then you
must
include the parameter name usin ":=". I also thing if you are need a
file
name not a Path. I think you want to concatenate the path with the
filename.

oApp.DoCmd.TransferDatabase _
TransferType:=acExport, _
DatabaseName:="MS Access", _
dbTargetPath, _
acTable, _
tbSource, _
tbDestination

expression.TransferDatabase( _
TransferType, _
DatabaseType, _
DatabaseName, _
ObjectType, _
Source, _
Destination, _
StructureOnly, _
StoreLogin)
Click to expand...

Thanks to all,

I tried this

oApp.DoCmd.TransferDatabase TransferType:=acExport, DatabaseType:="MS
Access", DatabaseName:=dbTargetPath, ObjectType:=acTable,
Source:=tbSource, Destination:=tbDestination

but I get the same error.

Here attached the test wb to replicate the error


+-------------------------------------------------------------------+
|Filename: proveexport.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=104|
+-------------------------------------------------------------------+
 
D

Dave Patrick

Also note the DatabaseType should be "Microsoft Access" not "MS Access"


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
 
D

Dave Peterson

Nope.

The code could have used:
Set oApp = nothing

Dave said:
Yes, I missed that but isn't the last assignment for oApp what's important?

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Dave Peterson said:
I don't think so.

This is the line that's important:
Dim oApp As Access.Application
(removed the first few variables)

means early binding.

I wouldn't use this line with that declaration, but it's not important.
Set oApp = CreateObject("Access.Application")
Click to expand...
Click to expand...
 
D

Dave Peterson

I agree. And I would have used:

Dim oApp as object
Set oApp = CreateObject("Access.Application")
without a reference

or

Dim oApp As Access.Application
Set oApp = New Access.Application
with a reference

I think it leads to less confusion.



Dave said:
Still not a good practice to add unnecessary clutter and confusion. :)

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Dave Peterson said:
Nope.

The code could have used:
Set oApp = nothing
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import Excel data to Access table 9
Sende/receive email from Excel with Outlook 2
Access form from Excel 0
Excel 2003 with SP" 1004 error 4
mdb locked 1
Opening Access database 3
Open Access Macro, then put a value into a parameter field 1
DoCmd.TransferDatabase acExport to SQL server 2005 2

Top