Link tables; VBA - invalid arguement

[TRM]

I'm sure this is something really simple; as I've had it working. However,
I'm wondering if anyone can offer suggestions as to why & how to get rid of
the "invalid arguement" error message I am getting on the append statement.

THANKS!!

'set objects/"pointers"
Set MyDb = OpenDatabase("C:\Documents and Settings\home\Desktop\Tammys
Trial\Marketing928_BE.mdb")
Set db = CurrentDb

'for each table in "MarketingBE.mdb"
For Each MyTdf In MyDb.TableDefs
If Left(MyTdf.Name, 4) <> "MSys" Then
Set tdf = db.CreateTableDef(MyTdf.Name)
With tdf
.Connect = ";DATABASE = C:\Documents and
Settings\home\Desktop\Tammys Trial\Marketing928_BE.mdb"
.SourceTableName = MyTdf.Name
End With
db.TableDefs.Append tdf
End If
Next

 

[Ken Snell [MVP]]

I believe you need to use the .Name value:

db.TableDefs.Append tdf.Name

 

[TRM]

Well, I've tried that. I then get the error, "type mismatch". Thanks for
the response.

 

[Ken Snell [MVP]]

Sorry... need to stop going from memory on some things < g >.

You're not using the correct object:

db.TableDefs.Append MyTdf

 

[TRM]

I appreciate any and all suggestions! However, this one didn't work either.
I received "there is already an object by that name" - or something like
that. Just so I am certain, I had things backwards when I FIRST tried this -
maybe I'm confused again (I had this correct & working, but mistakingly
copied over it), so...

Here's what I'm expecting. "CurrentDB" is the database I have open and am
"pulling" (linking) the tables into. "MyDB" is the source database
containting the tables I would like to link to. "MyTdf" corresponds to the
tables in the source file and "tdf" is the table I am creating in the current
database; which is why I have "tdf" after the append statement.

I have looked this error up and see that it's possibly fixed with an update
- but I have checked, and I have all the updates. I also saw on another post
a similar error, which was unexplained, but was fixed simply by copying to a
new database... I have tried that too.

Thanks again!!!

 

[Ken Snell [MVP]]

The problem is that you are trying to add a new table that has the same name
as a table that already exists. You name the new table ("tdf") the same as
the current table (MyTdf.Name). You need to give the new table a different
name.

 

[TRM]

I'll try that. But, should it not make a difference since "tdf" is in a
different database? I got the same name error when I had the "MyTdf.name" in
there; otherwise I get the "invalid argument". I ask for my own
understanding! I am still (always!) learning!

Thanks!

 

[TRM]

FYI - I tried - still getting "invalid argument" message. Thanks again!!!!!

 

[Ken Snell [MVP]]

My brain is not in gear, it seems. OK - I've read through the entire code.
What you apparently seek to do is to create a link to the table in the other
database, right? If yes, why not just use the DoCmd.TransferDatabase method
to create the link:

DoCmd.TransferDatabase acLink, "Microsoft Access", _
"C:\Documents and Settings\home\Desktop\Tammys
Trial\Marketing928_BE.mdb", _
acTable, MyTdf.Name, MyTdf.Name
--

Ken Snell
<MS ACCESS MVP>

 

[TRM]

Um... well, I guess I just like to make things hard?! =) I have tried your
suggestion, and it works fine (again, learning!)! Thanks so much!

Just because I like to know more than one way of doing things, if you, or
someone else, comes up with an answer to the other code, please respond. I'm
curious.

Otherwise - this works great! Ken, I appreciate your time and efforts!

 

[Ken Snell [MVP]]

Tables beginning with MSys are "system" tables within every ACCESS database.
Thus, your database already contains a table with the same name as the table
to which you want to link.

 

[Ken Snell [MVP]]

Ignore the post I made a bit ago about MSys tables.... erroneously posted --
and not on target for what your code is doing. My apology.

 

[Ken Snell [MVP]]

I don't see anything obviously wrong with the code that you were trying to
use here. Might be worthwhile, when you test it again and it breaks on that
line, that you use the Immediate Window and find out what the values of
tdf.Name, tdf.SourceTableName, and tdf.Connect are at that moment. Might
give us a clue.

--

Ken Snell
<MS ACCESS MVP>

 

[Brendan Reynolds]

The problem in the original code was the spaces around the "=" sign in the
Connect string ...

Public Sub TestSub()

Dim dbSource As DAO.Database
Dim dbTarget As DAO.Database
Dim tdfSource As DAO.TableDef
Dim tdfTarget As DAO.TableDef

Set dbSource = OpenDatabase("C:\DSDATA\Source.mdb")
Set dbTarget = CurrentDb

For Each tdfSource In dbSource.TableDefs
If Left(tdfSource.Name, 4) <> "MSys" Then
Set tdfTarget = dbTarget.CreateTableDef(tdfSource.Name)
With tdfTarget

'Problem was here.
'.Connect = ";DATABASE = C:\DSDATA\Source.mdb"
.Connect = ";DATABASE=C:\DSDATA\Source.mdb"

.SourceTableName = tdfSource.Name
End With
dbTarget.TableDefs.Append tdfTarget
End If
Next tdfSource
dbSource.Close
RefreshDatabaseWindow

End Sub

 

[Ken Snell [MVP]]

Thanks, Brendan... I had wondered about that, but ... well, other tasks
called.... < g >

 

[TRM]

Thanks to both of you! I know, it's a month later - the other code is
working, and I got side-tracked, so I didn't get back here. It's good to
know though!