How do I Link...............

[Bill]

I just completed a successful DAO creation
of a table and its fields in the backend mdb.

How do I "Link" the front end Currentdb to
the new table?

Probably staring me in the face, but I'm not
finding it.

Thanks,
Bill

 

[John W. Vinson]

I just completed a successful DAO creation
of a table and its fields in the backend mdb.

How do I "Link" the front end Currentdb to
the new table?

Probably staring me in the face, but I'm not
finding it.

Thanks,
Bill

To do it manually open the frontend database and use File... Get External
Data... Link (2003 and earlier) or the External Data tab, Link (2007 - 2010).

Programmatically, use the TransferDatabase method to link it.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

Hi John,
I get a runtime error:

2507: The type isn't an installed database type or doesn't
support the operation you choose.

with the statement:

DoCmd.TransferDatabase acLink, , IPDatabase, acTable, "tblsermons"

The intent is to link the current front end mdb (where the code is running)
to the table tblsermons in the mdb IPDatabase.

The HELP text says that the database type defaults to Microsoft Access,
so I'm in mystery as to what the problem might be.

Thanks,
Bill

 

[John W. Vinson]

Hi John,
I get a runtime error:

2507: The type isn't an installed database type or doesn't
support the operation you choose.

with the statement:

DoCmd.TransferDatabase acLink, , IPDatabase, acTable, "tblsermons"

Reread the help:

DatabaseName Optional Variant. A ***string expression*** that's the full
name, including the path, of the database you want to use to import, export,
or link data.

Instead of IPDatabase you need something like

"C:\SomePath\IPDatabase.mdb"

If IPDatabase is a VBA Variant or String variable containing the full path and
filename, it'll work, but that's not evident from your code.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

IPDatabase is a string variable of the fully qualified
path and name of the backend, whose current value
is "c:\gcc\gcc data.mdb". With that, you can see
why I can't figure out what the problem might be.

Bill

 

[John W. Vinson]

IPDatabase is a string variable of the fully qualified
path and name of the backend, whose current value
is "c:\gcc\gcc data.mdb". With that, you can see
why I can't figure out what the problem might be.

Sorry I couldn't see that through my screen. Care to post your actual complete
code? Maybe someone will see something that I can't.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

Hi John,
I get the same 2507 error with the "LinkTest"
code below as I do with the code in the broader
scope of the application (InitVer9_Part1):

Option Compare Database
Option Explicit
Private Sub LinkTest()
Dim BEdatabase As Variant
BEdatabase = "c:\gcc\gcc data.mdb"
DoCmd.TransferDatabase acLink, , BEdatabase, acTable, "tblsermons"
End Sub

However, if you want to see tha actual application code,
here it is: (Note that IPDatabase is a global string variable
whose current value is "c:\gcc\gcc data.mdb".)

Private Sub InitVer9_Part1()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' TableDef the "Sermon Table" and add all of
' its fields. (Thanks Allen Browne.)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim db As DAO.database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

On Error GoTo Err_InitVer9_Part1

'Initialize the Sermons table.
Set db = DBEngine.Workspaces(0).OpenDatabase(IPDatabase)
Set tdf = db.CreateTableDef("tblSermons")

'Specify the fields.
With tdf
'AutoNumber: Long with the attribute set.
Set fld = .CreateField("SermonID", dbLong)
fld.Attributes = dbAutoIncrField + dbFixedField
.Fields.Append fld

'Append the required special date expression field
Set fld = .CreateField("SDate", dbText, 16)
fld.Required = True
fld.AllowZeroLength = False
.Fields.Append fld

'Append the Scripture book name field
Set fld = .CreateField("SBook", dbText, 20)
fld.Required = False
fld.AllowZeroLength = True
.Fields.Append fld

'Append the Scripture reference field
Set fld = .CreateField("SReference", dbText, 20)
fld.Required = False
fld.AllowZeroLength = True
.Fields.Append fld

'Append the field for presenters name
Set fld = .CreateField("SPresenter", dbText, 50)
fld.Required = False
fld.AllowZeroLength = True
.Fields.Append fld

'Append the field for comments
Set fld = .CreateField("SComments", dbText, 50)
fld.Required = False
fld.AllowZeroLength = True
.Fields.Append fld

'Append the field for the file type
Set fld = .CreateField("SFileType", dbText, 6)
fld.Required = True
fld.AllowZeroLength = False
.Fields.Append fld

'Append the field for the file name
Set fld = .CreateField("SFileName", dbText, 100)
fld.Required = True
fld.AllowZeroLength = False
.Fields.Append fld

End With

'Save the Sermons table.
db.TableDefs.Append tdf
Set fld = Nothing
Set tdf = Nothing

'Now link the front end to the new table.
DoCmd.TransferDatabase acLink, , IPDatabase, acTable, "tblsermons"



End_InitVer9_Part1:
Exit Sub

Err_InitVer9_Part1:
MsgBox Err.Number & ": " & Err.Description
UpdateStatus = False
Resume End_InitVer9_Part1

End Sub

 

[David-W-Fenton]

'Save the Sermons table.
db.TableDefs.Append tdf
Set fld = Nothing
Set tdf = Nothing

'Now link the front end to the new table.
DoCmd.TransferDatabase acLink, , IPDatabase, acTable,
"tblsermons"

The only thing that occurs to me is that you should probably clean
up and close the newly created database:

db.TableDefs.Append tdf
db.TableDefs.Refresh
db.Close
Set db = Nothing
Set fld = Nothing
Set tdf = Nothing

Then try your TransferDatabase.

 

[Bill]

Hi David,
The "Refresh" and "Close" made no difference.
Still scratching my head.
Thanks,
Bill

 

[Bill]

Could "References" be causing this issue to persist?

Currently I have:

Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Outlook 11.0 Object Library

Bill

 

[Bill]

SUCCESS!!!

I configured a statement formatted with all parameters up to and including
the destination name, and now it works. If I leave ANY of the so-called
optional (default) parameters out, it fails.

Bill

 

[John W. Vinson]

I configured a statement formatted with all parameters up to and including
the destination name, and now it works. If I leave ANY of the so-called
optional (default) parameters out, it fails.

VERRRRY interesting. Thanks for posting back! Must be a version-specific issue
because it certainly works in my testing (and in my use of transferdatabase).
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bill]

Hi John,
I'm at VBA 6.5 under Office 2003 SP3.
Thanks for all you help.
Bill

 

[David-W-Fenton]

The "Refresh" and "Close" made no difference.
Still scratching my head.

What happens if you then re-open the database in code and check the
TableDefs collection? Is the table you created listed there?

 

[Bill]

Hi David,
The problem got solved. Did you see my post to
John from the 28th?
Bill