Import code getting error

[Don Wiss]

In my macros I import from workbooks all the time. Now I want to import my
first table. I deleted the table first. Then any variation of an import
fails. I have tried two computers, as someone thought my Access might be
corrupted. The path below is correct. I can put it in Explorer and the
database opens and the target table exists.

With:
DoCmd.TransferDatabase acImport, "Microsoft Access", "I:\Fac DUOP\GL-Parms\GL-Master.mdb", acTable, "PO_LossCosts"
The error is:
"The object name '' you entered doesn't follow Microsoft Access
object-naming rules."

With:
DoCmd.TransferDatabase acImport, , "I:\Fac DUOP\GL-Parms\GL-Master.mdb", acTable, "PO_LossCosts"
The error is:
"The type isn't an installed database type or doesn't support the
operation you chose."

I am using Access 2002 for both databases. Searching on the web for these
errors doesn't find anything relevant.

I believe there is some macro builder facility in Access. I tried to find
it to see what code it created, but I couldn't find it. And again the web
was of no help.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Steve Schapel]

Don,

First of all, in Access this is a VBA procedure. A macro is a completely
different fish.

You have omitted the name of the destination table. If it will be the same
name in the destination database, you still need to specify, i.e.
DoCmd.TransferDatabase acImport, "Microsoft Access", "I:\Fac
DUOP\GL-Parms\GL-Master.mdb", acTable, "PO_LossCosts", "PO_LossCosts"

 

[Don Wiss]

First of all, in Access this is a VBA procedure. A macro is a completely
different fish.

Okay. It is VBA behind a Form. I'm an Excel programmer...

You have omitted the name of the destination table. If it will be the same
name in the destination database, you still need to specify, i.e.
DoCmd.TransferDatabase acImport, "Microsoft Access", "I:\Fac
DUOP\GL-Parms\GL-Master.mdb", acTable, "PO_LossCosts", "PO_LossCosts"

The help states that the destination table is optional. I added it. I get
the same errors.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Steve Schapel]

In that case, Don, I am very sorry but I can't see anything wrong with what
you have done, and I can't think of why you would be getting that error.

Does your code compile ok?

If it was mine, the next thing I would try is making a brand new Access
file, and try it again in there.

 

[Don Wiss]

In that case, Don, I am very sorry but I can't see anything wrong with what
you have done, and I can't think of why you would be getting that error.

And I get it with all three tables that I am trying to import.

Does your code compile ok?

How do I know?

If it was mine, the next thing I would try is making a brand new Access
file, and try it again in there.

Ok. I can try that tomorrow when I'm in the office. I've shut down the
laptop and packed it up. Friday is my last day. The laptop has to get
returned. I was hoping I could get this working before I leave. I believe I
can also try to push these tables from the other database. Either way would
be okay.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Steve Schapel]

Hi Don,

How do I know?

When in the VB Editor, select Compile from the Debug menu.

Ok. I can try that tomorrow when I'm in the office. I've shut down the
laptop and packed it up. Friday is my last day. The laptop has to get
returned. I was hoping I could get this working before I leave. I believe
I
can also try to push these tables from the other database. Either way
would
be okay.

Well, yes, TransferDatabase can be done as an export. I suppose you could
try that. But as far as I can see, there's something strange here, because
I think it should work exactly as you have done it.

 

[Don Wiss]

When in the VB Editor, select Compile from the Debug menu.

Yes. It compiles.

I created a new database. I create a one line subroutine with the
problematic line. I got the same error. Then I tried importing the tables
using the File Import menu and had no problem.

The database that I am importing from is some 170 MB, but that shouldn't be
a problem.

I have not yet had a chance to use Excel VBA to push it from one database
to the other. With one day of work left things are getting hectic. But from
a process point-of-view this would be fine. The Master database is almost
entirely created by Excel VBA which is used as a controller. After all the
data is processed I just need to transfer three of the tables to a
production database. The only thing missing in the controlling workbook is
it doesn't know the pathname to the production database, but of course this
is a trivial addition.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Don Wiss]

I have not yet had a chance to use Excel VBA to push it from one database
to the other.

I tried the following code from the controlling workbook. I got the same
2057 error "The type isn't an installed database type or doesn't support
the operation you chose." As I no longer work there I consider the case
closed, though if the solution magically appears I will pass it on. The
fellow taking it over will simply have to import the three tables by hand.

Sub PushToProduction()

Dim oAccess As Object, SourceDB As String
SourceDB = Range("Database").Value

Set oAccess = CreateObject("Access.Application")
oAccess.opencurrentdatabase Range("GLParms").Value

On Error Resume Next
oAccess.DoCmd.RunSQL "DROP TABLE LossCostDates;"
oAccess.DoCmd.RunSQL "DROP TABLE PO_LossCosts;"
oAccess.DoCmd.RunSQL "DROP TABLE PR_LossCosts;"

On Error GoTo 0
oAccess.DoCmd.TransferDatabase 0, , SourceDB, , "LossCostDates"
oAccess.DoCmd.TransferDatabase 0, , SourceDB, , "PO_LossCosts"
oAccess.DoCmd.TransferDatabase 0, , SourceDB, , "PR_LossCosts"

oAccess.Quit
Set oAccess = Nothing

MsgBox "Production tables transferred to GL-Parms.", vbExclamation, "Done"

End Sub

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Steve Schapel]

Don,

I am not able to comment on the Excel code.

But I have tested code in Access using the same syntax as you were using,
and trying with various configurtations of path and object names and Acces
versions, and it works consistently for me. So I am flummoxed completely.

I am asking some MVP colleagues, so keep an eye on this thread for a few
days, and I will poost back if anyone else comes up with a brainwave.

 

[Steve Schapel]

Don,

Earlier in the thread, we mentioned about putting in the Destination table
name in the (Access VBA) code. You said you had added it to your code, but
still got the same error. Can you please double-check this please?

 

[Don Wiss]

Earlier in the thread, we mentioned about putting in the Destination table
name in the (Access VBA) code. You said you had added it to your code, but
still got the same error. Can you please double-check this please?

I did try it. But I can't try it again. As I am no longer employed at this
company I had to turn in the laptop. I no longer have access to any of
their systems. Sorry.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Steve Schapel]

Don,

Oh well, looks like one mystery that will go unsolved. On the basis of my
testing, that was the only explanation I could come up with for the error
you were getting, was that there was something inadvertently incorrect in
the syntax of the code you were using, in particular with the Destination
table argument.