Set Form RecordSource Property to Table of External Database

[TGlagowski]

I am trying to write an MS ACCESS VBA application that has the code in one *.accdb file and the data itself in a separate *.accdb file.

I am trying to set the RecordSource property of a SplitForm to all the elements of a table (Table1) in the external database, but don't know how to properly express the combination <DBname>.<Table Name> for that function.

The following Code does NOT work, but almost does

    Dim myQuery As String
    myQuery = "Select * From Table1"
    Set gSplitViewForm = New Form_TestTable
    gSplitViewForm.RecordSource = myQuery
    gSplitViewForm.Visible = True
    gSplitViewForm.SetFocus

Error Message:
The record soruce "Select * from Table1" specified on this form or report does not exist.

The error message I'm getting basically says that Table1 doesn't exist. I see this as a problem naming Table1 as part of the external database. How do I do that?

Thanks... TGlagowski

[TheSmileyCoder]

The standard approach would be to use a linked table, allthough I do believe its possible to use a query in the way you have outlined. But before complicating matters I would like to make sure that you have a good reason to not use a linked table?

Linked tables function pretty much like normal tables in access. You can open them directly from your frontend, and manipulate them. The only difference is that they are located in your backend instead.

[TGlagowski]

My understanding of the error message is that the system doesn't think that Table1 exists... and it doesn't within the *.accdb that contains the VBA code... What I need is a way to express something like... ExternalDBname. Table1 when I set the Form.RecordSour ce property. Do you know what the syntax would be for this?
TGlagowski

[Mihail]

Try to create the query using the Query design view interface.
I am sure that you can't because you can't add Table1 to the design window. And you can't do that because (you are almost right) the system know that is not a table named Table1.

So, as Smiley teach you in post #2, link the Table1 to the current database. After you do that all will be Ok.
If you don't know how to inform us about Access version you use and someone will teach you.

[TGlagowski]

Using Win 7 64 Ultimate
Using Office 2010 MS Access
Is the best keyword for help "Linked Tables" ?
I'll take a look...
From what I read from you... the approach is...
Link Table1 from the external database to the CurrentDatabase ...
Is that correct?

[Mihail]

Ok.
I use 2007 but I think that the interface is the same in 2010.

So:
1) In your current database go to External Data tab of ribbon.
2) Under Import section click Access.
This action will open the Get External Data window.
3) Choose Link to data source by creating a linked table.
4) Click Browse...
This action will open the File Open window
5) Browse the database where you have Table1, select it and click Open.
This action will open the Link Tables window.
6) Select your Table1 then click OK.

That is all.

Good luck !

[NeoPa]

TGlagowski:
Link Table1 from the external database to the CurrentDatabase ...
Is that correct?

Yes it is.

[TGlagowski]

Thanks everyone for your responses.
The key information that you provided was the concept of "Linked Table".
Using that keyword, I searched the Microsoft Office Help files.
The article entitled TableDef.Source TableName Property (DAO) had the answers.

My working code example follows.
An external database "Example1.accdb " has a table "Table1" with 3 fields: ID (autonumber), DBKey (text), DBValue (text).
The goal is to associate a SplitView Form recordsource with this table, also the corresponding fields. This form has the same fields as the DB Table.

If anyone has any comments about how this code can be improved,
please pass them along.

    Dim myDBFileName As String
    Dim myLinkedTableDef As TableDef
    Dim myTableName As String
    Dim myQuery As String

    Rem Setup Linked Table
    myDBFileName = "Example1.accdb"
    myTableName = "Table1"
    Set myLinkedTableDef = CurrentDb.CreateTableDef(myTableName)
    myLinkedTableDef.Connect = ";DATABASE=" & myDBFileName
    myLinkedTableDef.SourceTableName = myTableName
    CurrentDb.TableDefs.Append myLinkedTableDef

    Rem Open Trace View Form (TestTable)
    Set gSplitViewForm = New Form_TestTable
    myQuery = "Select * From " & myTableName

    Rem Set RecordSource for Form
    gSplitViewForm.RecordSource = myQuery
    
    Rem Set ControlSource for Fields in Form
    gSplitViewForm.ID.ControlSource = "ID"
    gSplitViewForm.DBKey.ControlSource = "DBKey"
    gSplitViewForm.DBValue.ControlSource = "DBValue"
    
    Rem Set Properties of Form
    gSplitViewForm.Visible = True
    gSplitViewForm.SetFocus
    
    Rem Clean Up Linked TableDef
    CurrentDb.TableDefs.Delete myTableName

- TGlagowski

[NeoPa]

My only comment is to suggest that post #2 might be a candidate for Best Answer in light of your comments ;-)

[Mihail]

The code seems to be Ok.
But why all that work to do, via VBA, what Access can do in simple steps (after you link table as I show you) ?

Certainly is not a problem with the best answer. I know very well Smiley and his skills :) .
This is a conceptual problem.
May I ask you how you plan to proceed if you wish, later, to define a query based on this table ?

[TGlagowski]

Answers for MiHail.

Why VBA?
This discussion was about a prototype example which models the larger routine in a real application. I needed to know how to go about setting the Form.recordsour ce property to the table of an external database, also the Control.control source properties of the relevant controls.
I was unfamiliar with the concept of "linked table".
Yes, I agree that SmileyCoder had the first and best answer.
Once I understood that "linked table" was the key, I found my way.

I am building an MS ACCESS VBA application which prints Amateur Radio QSL cards from a Log Database of Radio Contacts. It performs a number of other functions as well. This log is created by yet another application known as Ham Radio Deluxe or HRD which uses the MS ACCESS database as a repository.

When printing a card, I want to query the database for the contacts that optionally match the callsign, date, time, frequency, band, mode. A query will be constructed from the supplied information to be run against the database. A list of matches will be displayed in the SplitForm and the one selected will be used for printing using VISIO. Simple MS ACCESS alone won't do the job, so VBA is used.

Defining a Query?
Another form (not shown in the prototype example) has a list of fields some of which are mandatory and some of which are optional as contribution to the query that will be synthesized for use against the database. The expected reply is a set of zero or more records from the table, one of which will be selected for printing the QSL card. If none are returned, the query will have to be reformulated.

FYI, check www.arrl.org for introduction to amateur radio, search on QSL cards as well, if you are interested.

Thanks...
TGlagowski (W1TR)

[TheSmileyCoder]

I am starting to guess from your answers that your probably an experienced programmer, but that you do not have that much experience in using Access. This is not meant to offend you, I am just trying to understand you, and your area of expertise.

Now the thing about using access, and what helps make it a great tool, is that can be used as a combined client/server tool. It can work both as the client as well as the database. This can be very usefull during rapid development, proof of concept stages of a project. And then later, should one choose one can move the backend (the data) out of the access file, and either into a seperate access file (as in your case) or onto a SQL server or similar.

You can setup the linked tables once and for all, directly through the access user interface (or by code as you do). Once this is done you can use the linked table in pretty much the same way as you would any internal table, except that to modify a linked tables design, you need to open up the backend, and have exclusive access to the backend table.

There is nothing wrong with creating the link through code as you do, but unless you have a good reason to use such an approach I dont recommend it. You simply end up adding extra VBA coding, maintenance and complexity to your project.

To add a link to a table in AC2010, you click External Data, and in the group "Import & Link" you click Access. This starts a wizard, and you need to make sure that the first option group you see is set to "Link to the data source by creating a linked table".


In most of the applications I have developed using Access, I have a setup where each individual user gets his/her copy of the frontend database. A frontend database will contain all my forms, queries, reports and code. The backend will be placed on the network drive, and contain all the tables (and nothing else). All the tables seen in the frontend, are actually linked tables. You can in this scenario consider the frontend a Client, and the backend the server.

I hope this helped to clarify some of the issues you are facing.

[TGlagowski]

Answers to SmileyCoder...
You have it right...
I am very experienced in general, coding in FORTRAN 2 and 4 since 1964... IBM 1620, 1401, 7040, PDP-5 etc...
MEEE embedded, VLSI, kernel mode / PhD CS and knowledgeable about C++ and its many predecessors. Taught EECS SW/ENGR at WSU in the 90s.
And...INexperie nced in Access and some of the Office Suites and some of the newer programming environments.

The reason I want to do the linked table in VBA vs MS Access design interface is that I have MANY databases (several dozen) that I want to be able to dynamically link to, also several different TYPEs of database from different applications (3 or so with different schemas)...

This is NOT a commercial application or even shareware (for the moment anyway) and isn't high quantity of transactions like the financial business, etc...

My lack of knowledge is of the VBA object model for the various Office Suite applications, etc. So I don't know what the levers (API functions) that are available to make things happen !
Online / WEB information has become much better in the last few years but I just haven't had time to read it all.

The biggest thing you folks helped me with was to recognize that "linked tables" were the key to what I was trying to do, and then I looked up online help regarding that and proceeded to have success. Thank you very much for having patience with an old fart!

TGlagowski / W1TR

[Mihail]

I think that this link also can be a help for you:

http://bytes.com/topic/access/insights/876985-relinking-odbc-tables-using-vba#post3525573

[TheSmileyCoder]

Hi TGlagowski
Thank you for bearing with me, as I continue to drum on you, even though you seem satisfied with the solution you have. I just wanted to make sure you were using the code for the right reasons, instead of simpling setting up the linked tables from the start.
Your expressed requirements of dynamically linking backends is such a case, and I do feel satisfied that your doing it for the "right" reasons and not a lack of understanding of the mechanics of access now.




I just want to add one more bit of information to the pack here, as I do believe its relevant to your case, and might simplify it for you.

I finally managed to find a article I was looking for. I remember seing somewhere that you can actually write the database path+name directly into the SQL statement, the problem was being able to get the right collection of words for googling it, so I could find it again.

Try taking a look at this article by Crystal Long, posted at Allan Browne's webpage. http://allenbrowne.com/binary/Access...Chapter_06.pdf Especially look at page 2 of the pdf.

I tried it myself and were able to use like so with a query I created and wrote the SQL directly:

SELECT * FROM tbl_ClientVersion in 'C:\Test\Test.mde'

I noticed upon returning to design view, that the query actually has a property called Source Database, in which my "C:\Test\Test.m db" was now actually written.