Add Linked Table with VBA

[JaedenRuiner]

Alright, this just doesn't make any sense.

I have two MDBs, one is the front end application, the other is the actual table database.

Some modifications have arisen that basically require me to add a few things to the "table" database, so the MDE application will perform according to client specifications. Well, I don't want to toast the data that already exists in the database, nor do I want to deal with copying the data, so I figured, I would write an Update script, that would simply add the columns and constraints to the existing table and add the primary key parent table to the table database, and then add the linked table definition to the mde database.

So, being more of a SQL Server fan, i perfer sql , so I use it to create the table.

set db = OpenDatabase(path & dbfile)

sql = "Create table X (...)"
db.Execute SQL
sql = "ALTER Table Y ADD Column X"
db.execute sql

this part works fine (other than my post concerning the add constraint but this is about the link table so, i won't repost the same question).

So now, I've DB a database variable that is set tot he the "tables.mdb" and then CurrentDB which is the current open MDE file.
I try:

set tdf = CurrentDB.CreateTableDef("X", dbAttachedTable, "X", ";DATABASE=" & path & dbfile);
CurrentDb.TableDefs.Append tdf

the createtabledef says invalid argument. okay. why, dunno, but access is twitchy and not very helpful on error mearning, so i try instead:

set tdf = CurrentDB.CreateTableDef("X")
tdf.Connect = ";DATABASE=" & path & dbfile
CurrentDb.TableDefs.Append tdf

on the Append() call, i get "No field defined -- cannot append tabledef"
Well, frankly that's stupid. Of course id didn't define any fields. the fields are defined in the linked database, not this one. When i use the GUI to link a table I don't have tell it ANYTHING about the fields. only what the access mdb file is, and the table i want.

So, where is that command, the simple: "Add Link to this table on that MDB" command. I mean, if the GUI can do it that simply, I should be able to too. It also begs the question, I can create the links and then have my App.mdb file reference the new tables and columns i've added. But if i send over the mde file which is going to be looking at the "old" tables.mdb (that is missing those tables/columns) is that going to muck up my queries and forms, from the get go, or will it not be concerned since my first action is to update the tables.mdb with the script before any of those new features are accessed?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[HiTechCoach]

Alright, this just doesn't make any sense.

I have two MDBs, one is the front end application, the other is the actual table database.

Some modifications have arisen that basically require me to add a few things to the "table" database, so the MDE application will perform according to client specifications. Well, I don't want to toast the data that already exists in the database, nor do I want to deal with copying the data, so I figured, I would write an Update script, that would simply add the columns and constraints to the existing table and add the primary key parent table to the table database, and then add the linked table definition to the mde database.

So, being more of a SQL Server fan, i perfer sql , so I use it to create the table.

set db = OpenDatabase(path & dbfile)

sql = "Create table X (...)"
db.Execute SQL
sql = "ALTER Table Y ADD Column X"
db.execute sql

this part works fine (other than my post concerning the add constraint but this is about the link table so, i won't repost the same question).

So now, I've DB a database variable that is set tot he the "tables.mdb" and then CurrentDB which is the current open MDE file.
I try:

set tdf = CurrentDB.CreateTableDef("X", dbAttachedTable, "X", ";DATABASE=" & path & dbfile);
CurrentDb.TableDefs.Append tdf

the createtabledef says invalid argument. okay. why, dunno, but access is twitchy and not very helpful on error mearning, so i try instead:

set tdf = CurrentDB.CreateTableDef("X")
tdf.Connect = ";DATABASE=" & path & dbfile
CurrentDb.TableDefs.Append tdf

on the Append() call, i get "No field defined -- cannot append tabledef"
Well, frankly that's stupid. Of course id didn't define any fields. the fields are defined in the linked database, not this one. When i use the GUI to link a table I don't have tell it ANYTHING about the fields. only what the access mdb file is, and the table i want.

So, where is that command, the simple: "Add Link to this table on that MDB" command. I mean, if the GUI can do it that simply, I should be able to too. It also begs the question, I can create the links and then have my App.mdb file reference the new tables and columns i've added. But if i send over the mde file which is going to be looking at the "old" tables.mdb (that is missing those tables/columns) is that going to muck up my queries and forms, from the get go, or will it not be concerned since my first action is to update the tables.mdb with the script before any of those new features are accessed?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

How I normally ddp this is:

1) run a back end updater
2) replace the front end MDE with a new one that already has the new new tables linked.


I prefer to use DAO to create tables. DAO is what Access uses internally when working weith tables. It is more powerful that using SQL.

See:
DAO Programming Code Examples

and

Free Updating tools


Here is code that will automaticall relink or add links to all tables in a back end

Public Function LinkBackEnd(strFileName As String) As Boolean
On Error GoTo Err_proc
'Function creates linked tables to all tables (except "msys*" tables) in BE database defined by strFilename

Dim strSQL As String
Dim dbs As Database, tdf As TableDef
Dim dbs1 As DAO.Database, tdf1 As TableDef
Dim strTblName As String

Set dbs = OpenDatabase(Application.CurrentProject.FullName)
Set dbs1 = DBEngine.Workspaces(0).OpenDatabase(strFileName)

On Error Resume Next
For Each tdf1 In dbs1.TableDefs
strTblName = tdf1.Name
Debug.Print strTblName
If Left(strTblName, 4) <> "msys" Then
dbs.TableDefs.Delete strTblName

Set tdf = CurrentDb.CreateTableDef(strTblName)
tdf.Connect = ";DATABASE=" & strFileName
tdf.SourceTableName = strTblName
CurrentDb.TableDefs.Append tdf
End If
Next tdf1
On Error GoTo Err_proc

LinkBackEnd = -1

Exit_proc:
On Error Resume Next
Set dbs1 = Nothing
Set dbs = Nothing
Exit Function

Err_proc:
MsgBox err.Description, , CurrentDb.Properties("strTitle")
Resume Exit_proc

End Function

 

[DCrake]

Have you references Microsoft DAO n.nn Object Library in Tools and References?

 

[JaedenRuiner]

Thank you for your response. I will take it under advisement. However, there are a few points.

First
I would use DAO if Microsoft would DOCUMENT IT but since their online and context documentation sucks, I only use a few things here or there. Basically, when I type:

   Dim rs as DAO.Recordset
   rs.

Nothing pops up after the period. No Code Insight, no code completion. I Hit f1 on recordset and I get I a few convoluted pages that don't really explain diddly. For example, the FindFirst method there is no documentation that tells how to determine if the Recordset actually found anything. I had to run a findfirst over a dozen times, comparing the Watch List Property Values to find out what changed after a success and failure of a find, to discover that the NoMatch property indicates if a match was not found. Now, this is not your fault, this is Microsoft's fault, but frankly I don't have the time to search and search and search, to only get responses on access 2007 instead of 2003 like i requested, with still no clear cut syntax diagram documentation of the classes.
Creating a table, however: DAO More Powerful? eh, matter of opinion. Could very well be. But Quicker? Absolutely not.

Create Table MyTable (
   ID int IDENTITY(1,1) NOT NULL,
   Name nvarchar(150) NOT NULL,
   Address nvarchar(250) NULL,
   City Nvarchar(100) NULL,
   State Nvarchar(2) NULL,
   Zip Nvarchar(10) NULL,
   SomeCode int,
   
    CONSTRAINT PK_MyTable PRIMARY KEY CLUSTED ([ID] ASC) ON [PRIMARY],
    CONSTRAINT  FK_SomeTable_MyTable FOREIGN KEY ([ComeCode])
      REFERENCES SomeTable ([ID])
      ON UPDATE CASCADE
     ON DELETE cASCADE
)

Hit F5 and the table is created. I wrote that script in 15 seconds, and it will execute in less than 1. Granted this is using Transact SQL (for sql server) but the SQL syntax is so similar I barely notice the difference, and I can write this script, create the table, and actually be using it easily 5 times faster than the GUI in Access will allow me to create and set properties of a table there. I've created entire databases of 5-10 tables, queries, triggers, stored procedures, and relationship constraints in a matter of minutes. Access GUI takes a bit longer to do. I prefer sql because I know sql and I'm a programmer, so writing code is easier for me to work with than interface gui's. I've found in some circumstances it was easier to create a mdb with a vb module that created the entire back end database with sql scripts as opposed to using the gui environment, because all i needed to do was write a string, not work with all the weird intrinsic DAO properties that aren't documented well enough to explain how to use them, nor did i have to move back and forth betwen different slower graphical windows that won't remember simple window dimensions. the Table design view opens the same every time, and I constantly have to resize it so I can see other things in the window. Once the tables are made with SQL I can go through and set the Lookup properties for combobox'ed fields, and inputmasks for string fields, etc. Which I know can probably be set via DAO, but like I said i don't have the time to look for documentation and I already know how to type a SQL script.

Secondly,
I already know how to refresh existing links. I didn't even need to post a question on that one as a simple search in google provided my code examples perfect for educating me on how to refresh already linked tables.

My Question was how to ADD a link.

My way may not be the Best way to do something, but it is what I asked. I truly do not mind advice on how to do something, but not at the expense of answering the question. If you ask 20 programmers how to do something, you will get 40 different answers, and none of them are right or wrong, they are just different. I need to know how to ADD a link. If I can't "Add" the link with VBA, well then that is simply explained: "sorry dude, you can't do that.", and then I will have to think of other ways to achieve my aim.
I will look into your suggestions later, once I know how to ADD a link.

Sincerely,
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[namliam]

Basically, when I type:

   Dim rs as DAO.Recordset
   rs.

Nothing pops up after the period. No Code Insight, no code completion.

Because you havent referenced DAO, while you are in the VBA builder, got to Tools > references
Then add the Microsoft DAO x.y Object Library and these problems disappear like snow for the sun. Without adding the reference your using Late binding, which has its advantages and its drawbacks, adding the reference makes for early binding and enable you to use autocomplete features and help like things much easier.

I Hit f1 on recordset and I get I a few convoluted pages that don't really explain diddly. For example, the FindFirst method there is no documentation that tells how to determine if the Recordset actually found anything.

Looking at the example code in the help found in F1:

Sub FindMatch(rstTemp As Recordset, _
    strFind As String)

    Dim varBookmark As Variant
    Dim strMessage As String

    With rstTemp
        ' Store current record location.
        varBookmark = .Bookmark
        .FindFirst strFind

        ' If Find method fails, notify user and return to the 
        ' last current record.
        If .NoMatch Then
            strMessage = _
                "Not found! Returning to current record." & _
                vbCr & vbCr & "NoMatch = " & .NoMatch
            MsgBox strMessage
            .Bookmark = varBookmark
        End If

    End With

End Sub

This may be result of the same missing reference

My Question was how to ADD a link.

CurrentDb.TableDefs is in control of any existing table defs

CurrentDb.CreateTableDef will create one for you. Actual table or linked no matter.

 

[HiTechCoach]

Nothing pops up after the period.

That is probably due to not setting the reference to the DAO library.

Creating a table, however: DAO More Powerful? eh, matter of opinion. Could very well be. But Quicker? Absolutely not.

There are things you can do with DAO that are just not possible using SQL. That is why I said it is more powerful. I use it to create complete back ends. Also for documenting back ends. Because DAO can also read table Defs, I can read from one back end and apply any the changes to another back end.

Quicker to write the code?
That depend on your experience level. I used to use SQL like you do. I have since changed to DAO. I have learned how to use DAO so now I am much faster with DAO than with SQL.

Note: In my test for execution speed, DAO was always faster.

Secondly,
I already know how to refresh existing links. I didn't even need to post a question on that one as a simple search in google provided my code examples perfect for educating me on how to refresh already linked tables.

My Question was how to ADD a link.

The example code I posted does add links, not just refresh them. It deletes all the linked tables with matching names. It adds links to ALL tables in a back end even if they did not previously exist. Actually, It does not refresh the links at all. It only Adds links, which is what I thought you ask for.

 

[JaedenRuiner]

The example code I posted does add links, not just refresh them. It deletes all the linked tables with matching names. It adds links to ALL tables in a back end even if they did not previously exist. Actually, It does not refresh the links at all. It only Adds links, which is what I thought you ask for.

I noticed that and tested the appropriate code first, but it didn't work. I still got an error. I will re-run and test again, since the only apparent difference between your code and mine was the existence of the "SourceTableName" property setting.
Perhaps My computer was in a funk, as it has been known to do. with all development environments...

J"SD"a'RR

 

[JaedenRuiner]

Because you havent referenced DAO

Utter impossibility.

DAO 3.6 is Automatically added and reference by Access it self. It can only not be referenced if it is forcibly removed from the references list. Thus, since every project of mine pretty much uses the defaults, they all reference DAO 3.6 and I have never removed it.

Just to be sure, I just double checked and sure enough, DAO 3.6 is checked and appropriately referenced.

Additionally any help i try to receive on the DAO Recordset, either gives me ADODB or the Recordset "Property", From the RecordSet "Property" I click on the link to DAO Recordset and get "Page not Found".

J"SD"a'RR

 

[HiTechCoach]

Utter impossibility.

DAO 3.6 is Automatically added and reference by Access it self. It can only not be referenced if it is forcibly removed from the references list. Thus, since every project of mine pretty much uses the defaults, they all reference DAO 3.6 and I have never removed it.

Just to be sure, I just double checked and sure enough, DAO 3.6 is checked and appropriately referenced.

Additionally any help i try to receive on the DAO Recordset, either gives me ADODB or the Recordset "Property", From the RecordSet "Property" I click on the link to DAO Recordset and get "Page not Found".

J"SD"a'RR

What version of Access are you running including the SP/SR Version?

What version of Windows?

 

[JaedenRuiner]

Windows XP SP3
Access 2003
11.5614.5606

 

[JaedenRuiner]

My Case and Point with DAO and Documentation:

Syntax

Set tabledef = database.CreateTableDef (name, attributes, source, connect)

The CreateTableDef method syntax has these parts.

Part Description

• tabledef An object variable that represents the TableDef object you want to create.
• database An object variable that represents the Database object you want to use to create the new TableDef object.
• name Optional. A Variant (String subtype) that uniquely names the new TableDef object. See the Name property for details on valid TableDef names.
• attributes Optional. A constant or combination of constants that indicates one or more characteristics of the new TableDef object. See the Attributes property for more information.
• source Optional. A Variant (String subtype) containing the name of a table in an external database that is the original source of the data. The source string becomes the SourceTableName property setting of the new TableDef object.
• connect Optional. A Variant (String subtype) containing information about the source of an open database, a database used in a pass-through query, or a linked table. See the Connect property for more information about valid connection strings.

Using the Attributes, Source, and Connect parameters do not work.

      'db is assigned to the Data database that's loaded
      Set tdf = db.CreateTableDef("lst_Providers", dbAttachedTable , "lst_Providers", ";DATABASE=" & db_Prefs("Path") & db_Prefs("DB"))
      CurrentDb.TableDefs.Append tdf

I have tried the above in such fashion:

• using db.CreateTableDef(), where db is the remote database i'm linking in
• using CurrentDb.CreateTableDef(), thinking that since i'm appending it to the current I'll use the current to create
• With the dbAttachedTable attribute, which causes an Invalid Parameter Error
• without the dbAttachedTable attribute

In ALL circumstances the tdf assigned by the statement does not have it's SourceTableName, or Connect property defined. What's the point of the parameters, or even claiming to support them when they don't work.
In a true syntax diagram, all options should be clearly defined and marked. If i'm not supposed to provide a name, since the name comes from the source table it should say so. If the attributes is read/write it should say so. It doesn't. Using the attributes causes an error, and either way the parameters except for the name parameter aren't used, so why include them. Why lead people on to believe I can create the tabledef in 1 statement when i can't?

J"SD"a'RR

 

[gemma-the-husky]

back to the beginning

you need to use SET to get intellisense

not

dim rst as recordset
rst. as you suggested

but

dim rst as recordset
dim dbs as database

set dbs=currentdb
set rst = dbs.openrecordset "qryname"

and now you WILL get intellisense.


-----------
having said that, the intellisense with DAO is somewhat limited. There is an amazing amount you can do with DAO. I have O'Reilly DAO Object Model - its 382 pages long!

 

[JaedenRuiner]

dim rst as recordset
dim dbs as database

set dbs=currentdb
set rst = dbs.openrecordset "qryname"[/B]
and now you WILL get intellisense.

Uh, no. SET is only used when Assigning an object value to a variable. So yes, you use SET rst or SET dbs in that situation because both are receiving object variables. If my post confused you because I did not include an assignment line in between my variable declaration and usage, mea culpa. Intellisense appears the MOMENT you add the period class separator for accessing class (object) properties and methods. You would NOT do:

Set rst.FindFirst "ID = 35"

To get the intellisense on the FindFirst or to provide code completion after typing "rst.FindF" the keyword "set" is never involved. Methods and properties have nothing to do with SET.

having said that, the intellisense with DAO is somewhat limited. There is an amazing amount you can do with DAO. I have O'Reilly DAO Object Model - its 382 pages long!

Yes, and that is the point. The Intellisense is limited, the online documentation (the help window) is severly limited, and frankly Microsoft has not updated the VBA editor in 15 years. I used the same editor and had the same complaints for Office 97 that I use and have to this day in office 2003 (and to some extent this extends to what I've heard as well about 2007, though I have not used it personally). Frankly, that's unacceptable in my standards. I mean, how much difficulty could it involve to cross departments and have some of the Visual Studio developers provide a better editor for the VBA back end of Office. ESPECIALLY with Access, which is primarily a development environment. You can create an Access DB without using VBA, but most times you will find people want to create a form or two to interface with their databases, and on the professional level, you especially create forms, vba modules, and compile the code to an MDE. How difficult could it be, to take the same dedication that exists in the Context help for Visual Studio with regards to C++, C#, VB.Net, ASP.NEt and the power of that Help system and expand it to include VBA. I mean, seriously, that is a drop of water on the ocean. Yet they don't do it, and we keep suffering through this design without making enough of a fuss about it to inspire a change.
*shrug*
J"SD"a'RR