Link front end db to back end table, part 2 Rod 26.09.2006 20:16:02 Alex, I'm resubmitting this, thinking it may have gotten lost in the posting tree... I inserted db.TableDefs.Delete(strInternalTableName) before line Set tdf = db.CreateTableDef(strInternalTableName) I receive this vb run-time error 3265: Item not found in this collection. How can a table be deleted if it doesn't exist? Rod "Alex Dybenko" wrote: [Quoted Text] > Hi, > before db.CreateTableDef(strInternalTableName) > you have to delete this table in FE: > > db.TableDefs.Delete(strInternalTableName) > > look at this code for more info: > http://www.mvps.org/access/tables/tbl0009.htm > > -- > Best regards, > ___________ > Alex Dybenko (MVP) > http://alexdyb.blogspot.com > http://www.PointLtd.com > > > "Rod" <Rod[ at ]discussions.microsoft.com> wrote in message > news:2E55898C-B9C1-47AE-A4C4-8AF9D5A75C86[ at ]microsoft.com... > > I'm trying to link a front end db to a table in a back end db. This is my > > code... > > > > 'Creates link to table SpringfieldLetters in smpdata.mdb > > Function fncLinkToSpringfieldLetters() > > Dim strInternalTableName As String > > Dim strExternalDB As String > > Dim strExternalTable As String > > Dim tdf As TableDef > > Dim db As Database > > > > Set db = CurrentDb() > > strInternalTableName = "SpringfieldLetters" > > strExternalTable = "SpringfieldLetters" > > Set tdf = db.CreateTableDef(strInternalTableName) > > > > tdf.Connect = ";DATABASE=" & "c:\lacc\smpdata.mdb" > > tdf.SourceTableName = strExternalTable > > db.TableDefs.Append tdf > > > > End Function > > > > When I run the function I receive the vb run-time error 3012: Object > > "SpringfieldLetters" already exists. > > > > If I change the name of the linked table to a distinct name, it works > > fine. > > However, I want the linked table to have the same name as the back end > > table. > > > > I tried a workaround... > > > > DoCmd.CopyObject , "SpringfieldLetters", acTable, "SpringfieldLetters2" > > > > trying to rename the linked table with the distinct name > > "SpringfieldLetters2" to the name of the table in the back end db. I > > received the vb run-time error 2501: The CopyObject action was canceled. > > > > I'd appreciate your help! > > > > Rod

Re: Link front end db to back end table, part 2 "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> 26.09.2006 20:36:13 Just add On Error Resume Next before the delete statement, and On Error Goto 0 (or your error hander) afterwards. That way, if the table exists, it'll be deleted; if it doesn't, it'll just continue onwards. "Rod" <Rod[ at ]discussions.microsoft.com> wrote in message news:930E8923-B801-4235-BD8B-9B5F0BBCFCAD[ at ]microsoft.com... [Quoted Text] > Alex, > > I'm resubmitting this, thinking it may have gotten lost in the posting > tree... > > I inserted > db.TableDefs.Delete(strInternalTableName) before line > Set tdf = db.CreateTableDef(strInternalTableName) > > I receive this vb run-time error 3265: Item not found in this collection. > > How can a table be deleted if it doesn't exist? > > Rod > > "Alex Dybenko" wrote: > >> Hi, >> before db.CreateTableDef(strInternalTableName) >> you have to delete this table in FE: >> >> db.TableDefs.Delete(strInternalTableName) >> >> look at this code for more info: >> http://www.mvps.org/access/tables/tbl0009.htm >> >> -- >> Best regards, >> ___________ >> Alex Dybenko (MVP) >> http://alexdyb.blogspot.com >> http://www.PointLtd.com >> >> >> "Rod" <Rod[ at ]discussions.microsoft.com> wrote in message >> news:2E55898C-B9C1-47AE-A4C4-8AF9D5A75C86[ at ]microsoft.com... >> > I'm trying to link a front end db to a table in a back end db. This is >> > my >> > code... >> > >> > 'Creates link to table SpringfieldLetters in smpdata.mdb >> > Function fncLinkToSpringfieldLetters() >> > Dim strInternalTableName As String >> > Dim strExternalDB As String >> > Dim strExternalTable As String >> > Dim tdf As TableDef >> > Dim db As Database >> > >> > Set db = CurrentDb() >> > strInternalTableName = "SpringfieldLetters" >> > strExternalTable = "SpringfieldLetters" >> > Set tdf = db.CreateTableDef(strInternalTableName) >> > >> > tdf.Connect = ";DATABASE=" & "c:\lacc\smpdata.mdb" >> > tdf.SourceTableName = strExternalTable >> > db.TableDefs.Append tdf >> > >> > End Function >> > >> > When I run the function I receive the vb run-time error 3012: Object >> > "SpringfieldLetters" already exists. >> > >> > If I change the name of the linked table to a distinct name, it works >> > fine. >> > However, I want the linked table to have the same name as the back end >> > table. >> > >> > I tried a workaround... >> > >> > DoCmd.CopyObject , "SpringfieldLetters", acTable, "SpringfieldLetters2" >> > >> > trying to rename the linked table with the distinct name >> > "SpringfieldLetters2" to the name of the table in the back end db. I >> > received the vb run-time error 2501: The CopyObject action was >> > canceled. >> > >> > I'd appreciate your help! >> > >> > Rod >