Link front end db to back end table Rod 26.09.2006 15:35:02 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 "Alex Dybenko" <alexdyb[ at ]PLEASE.cemi.NO.rssi.SPAM.ru> 26.09.2006 15:44:05 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... [Quoted Text] > 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 Rod 26.09.2006 16:14:03 Alex, The table doesn't exist in the front end db. That's what's confusing to me. I'll give a try. 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 Rod 26.09.2006 16:21:03 Alex, 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 "Alex Dybenko" <alexdyb[ at ]PLEASE.cemi.NO.rssi.SPAM.ru> 27.09.2006 07:23:36 Hi, then you can check if table exists, before you create it. or just use the code I gave link to -- Best regards, ___________ Alex Dybenko (MVP) http://alexdyb.blogspot.com http://www.PointLtd.com "Rod" <Rod[ at ]discussions.microsoft.com> wrote in message news:F809778F-E7B9-4903-A4D5-2742404D06C2[ at ]microsoft.com... [Quoted Text] > Alex, > > 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 >> >>