Urgent: Linked Tables - converting current tables to linked tables - automatic update?

Posted on 2007-07-24
Last Modified: 2013-11-27
Hi i am using access 2003,

I have a database with a lot of tables. I have been searching for a way for a user to easily update these tables themseleves from excel files. This way a user could update tables from the client point. I have been searching and can not find a simple solution to this problem. I guess using linked tables would be the easiest way. So i will create excel files at a server location. The user will just have to update data there than. Will my linked files update automatically? How do i convert my current tables to linked tables?

If you have a better idea, i am open to suggestions. This is very urgent. This must be implement by the end of the week.
Question by:jtovar3
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    You can't update an external data source from Excel - used to be able to but not any longer - some kind of patent infringement issue.

    I do this by using ADO. Here are some notes on how to use ADO to pull data from a database into Excel and push data back into the database.

    The following text and sample code illustrates how to:

       -> open a database connection
       -> use a stored procedure to perform a query
       -> execute any SQL command against a database
       -> open a recordset using a custom query
       -> open a recordset using a table name
       -> check for an empty recordset
       -> read all records in a recordset
       -> add a record to a recordset
       -> delete a record from a recordset
       -> copy a recordset with headers to a worksheet
       -> close a recordset and database

    This sample code, except for Open database method, can be used with any database such as Access, SQL Server, or Oracle. When using a database, most interaction happens via a recordset. Data is manipulated almost entirely using Recordset objects. Any number of Recordset objects can be created and used at the same time - each representing a different query or the same query. Different Recordset objects can access the same tables, queries, and fields without conflicting.

    After opening a Recordset the Recordset can contain zero or more records. One record in the Recordset is always the current record except when the Recordset BOF or EOF property is true in which case no record is the current record. The current record is the record that is affected by any record-specific methods. To move amongst the records in a Recordset use the MoveNext, MovePrevious, MoveLast, and MoveFirst Recordset methods. A specific record can be made the current record by setting the AbsolutePosition property to the index number of the desired record. Fields in the current record are access as illustrated below.

       Value = MyRecordset!Field1
       MyRecordset!Field2 = Value + 1

    When the current record is changed use the Update Recordset method to apply the changes to the database. Use the Add method to add a new record and the Delete method to delete the current record.

    Before writing any ADODB code the data objects library "Microsoft ActiveX Data Objects x.x Library" must be referenced in the VBA project (Tools->References).

    For additional information on the ADODB interface see the MSDN pages at:

    Sample code:

       Dim MyDatabase As ADODB.Connection
       Dim MyCommand As ADODB.Command
       Dim MyRecordset As ADODB.RecordSet
       Dim Column As Long

       ' Open database connection
       Set MyDatabase = New ADODB.Connection
       MyDatabase.CursorLocation = adUseClient
       MyDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\full\path\to\database.mdb'; User Id=admin; Password=;"
       ' For more information about Open syntax:
       '   and navigate to Open Method (ADO Connection)
       '   Additional help constructing connection strings can be found at

       ' Query database using stored procedure (requires command object)
       Set MyCommand = New ADODB.Command
       Set MyCommand.ActiveConnection = MyDatabase
       MyCommand.CommandText = "qrySomeQuery" ' <- name of procedure
       MyCommand.CommandType = adCmdStoredProc
       With MyCommand
          .Parameters.Append .CreateParameter("QueryTextParam", adVarChar, adParamInput, 10, "Value")
          .Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LongValue)
          .Parameters.Append .CreateParameter("QueryDateParam", adDate, adParamInput, , DateValue)
          .Parameters.Append .CreateParameter("QueryDateTimeStampParam", adDBTimeStamp, adParamInput, , DateTimeValue)
          .Parameters.Append .CreateParameter("BooleanParam", adBoolean, adParamInput, , BooleanValue)
          ' For more information about CreateParameter syntax:
          '   and navigate to CreateParameter Method
       End With
       ' Open recordset using command object
       Set MyRecordset = New ADODB.Recordset
       MyRecordset.Open MyCommand, , adOpenDynamic, adLockPessimistic
       ' For more information about Open method syntax:
       '   and navigate to Open Method (ADO Recordset)
       ' Build a custom query using command object
       Set MyCommand = New ADODB.Command
       With MyCommand
          Set .ActiveConnection = MyDatabase
          .CommandType = adCmdText
          .CommandText = "SELECT * From tblMyTable WHERE (tblMyTable.MyID = 1)"
       End With
       MyRecordSet.Open MyCommand, , adOpenDynamic, adLockReadOnly

       ' Execute any SQL statement
       MyDatabase.Execute "INSERT INTO TableName (Field1, Field2) VALUES ('" & Range("A1").Value & "','" & Range("A2").Value & "')"

       ' Open a recordset by specifying specific table (no query)
       MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
       ' Open a recordset by specifying query without using command object
       MyRecordset.Open "SELECT * FROM MyTable", MyDatabase, adOpenDynamic, adLockPessimistic

       ' Test for no records
       If MyRecordset.BOF And MyRecordset.EOF Then
          MsgBox "No records in table"
       End If

       ' Determine total records
       MsgBox "Total records: " & MyRecordset.RecordCount
       ' Look at all records in record set
       While Not MyRecordset.EOF
          MsgBox "Record number: " & MyRecordset.AbsolutePosition

       ' Copy the entire recordset to a worksheet (this technique does not copy field names)
       Sheets("Sheet1").[A2].CopyFromRecordset MyRecordset
       ' Create headers and copy data
       With Sheets("Sheet1")
          For Column = 0 To MyRecordset.Fields.Count - 1
             .Cells(1, Column + 1).Value = MyRecordset.Fields(Column).Name
          .Range(.Cells(1, 1), .Cells(1, MyRecordset.Fields.Count)).Font.Bold = True
         .Cells(2, 1).CopyFromRecordset MyRecordset
       End With

       ' Update current record
       MyRecordset!Field1 = "Some data"
       MyRecordset!Field2 = "Some more data"

       ' Move specific fields from current record to worksheet
       With Sheets("Sheet1")
          Cells(Row, "A") = MyRecordset!Field1
          Cells(Row, "B") = MyRecordset!Field2
       End With
       ' Add new record and set field values
       MyRecordset!Field1 = "Some data"
       ' Delete current record

       ' Close recordset

       ' Close database

    LVL 58

    Expert Comment

    Woot, zorvek! This looks like several hours of work...


    You can of course move your current application from Access table to Excel linked tables, but that is, generally speaking, a very bad idea (I should have capitalized that).

    * Excel sheets aren't tables (no data-type checking, no indexes)
    * Excel sheets don't allow any form of (serious) validation
    * Excel sheets can't be updated from within Access

    If your client insists upon updating data from Excel, you can count on about two or three weeks of full-time development. I can tell, I just went through the process with two clients.

    If you are looking for a quicker solution: offer a crash-course in Access. Learning to update date in Access tables, or in some simple forms you can create, requires about one or two days. Users of Excel are usually quite quick in getting the main differences (and in appreciating the benefits).

    Again: the solution you are thinking about means to move the "master data container" from Access to Excel. That is a no-no. Creating all the needed import and export routines to reliably update Access data from Excel sheets is a major development. Teaching how to use Access datasheets is a one day course.

    Take your pick.


    Author Comment

    zorvek... your way is to complicated for me to try to work through within a few days... it seems like it would work but i just do not have the skill to quickly understand and implement that

    harfang... so basically you are just recommending that i train my users to basically access the tables and update the data themselves? This is actually what i started thinking today. I was thinking of creating a form that would open a table the user selected... this way they would not have to go into the backend directly and could do this from the frontend application. What do you think?
    LVL 58

    Expert Comment

    If at all possible, create one form (datasheet or regular) for each of the editable tables. Create one "main menu" form with buttons or hyperlinks opening these forms. Using forms allows you to fine-tune what is allowed and what isn't (only addition of new records, edit but no delete, read-only, etc.)

    If everything is allowed, you can also directly open the table. No harm in that as long as you have thought through your data types, your validation rules, and your referential integrity rules. It's not all that hard, and training half a dozen users is much quicker than creating a reliable export-import feature to and from Excel.

    If you have time, show some simple queries as well. You might be starting new careers -- (^v°) Another all-time hit is to have several users edit the same table at the same time. Good laughs guaranteed!

    Access really allows applications at any level between: a couple of tables, queries, forms, and reports, accessed directly from the database window; and a full-blown application where you don't even realize you are working in Access. If you are under time pressure, rely on the rather good and Excel-user-friendly interface of raw Access.

    Good luck!

    Author Comment

    thanks i appreciate the tip and i am going to follow you on that one... one problem however is that i have 60 some odd tables... i think creating a from for eachone would be highly inefficient. however for other forms i have created i have made a list box or a combo box that lists the tables through there. I have a query with 3 fields. Autonumber, table name, and the user friendly table name. Is there a way to use this query to select it from a box and than have a command button to just open the table ? not sure if i explained myself well... sorry if i did not
    LVL 58

    Accepted Solution

    You combo having:

        Name: cboSelectTable
        Column Count: 3
        Column Widths: 0;0      (hide two columns)
        Bound Column: 2          (directly return the table name)

    And your command button using:

    Private Sub cmdOpenTable_Click()

        If IsNull(cboSelectTable) Then
            DoCmd.OpenTable cboSelectTable
        End If

    End Sub

    You should be rolling.

    However, notice that creating a form for a table takes about 20 seconds. Times 60 is a bit scary, but it's still only half an hour: select table, choose (Insert | Autoform) or find the [Autoform] button on the toolbar. Switch to design view, select mode (datasheet or otherwise), provide a friendly caption and save.

    But from the look of it (60 tables) you probably don't need that.


    Author Comment

    That code works perfectly.... i really appreciate the time.

    i probably dont need 60 tables (youre right)... but i am not experienced enough to figure out how to do it any other way. now that i have gotten my database developed and coded i am scared to go back and try to normalize it... but it is doing the job so far... if you have any advice for the tables itd be greatly appreciated... if not you have already helped me enough here... thank you i really appreciate it
    LVL 58

    Expert Comment

    Well, I'm afraid I can't normalize 60 tables through EE. That's really my paying job anyway (revamping access databases which have somehow become vital but where the author is no longer around).

    But my advise is always: don't be afraid to restart from scratch. Sounds scary, but all good databases I have seen have been rewritten entirely at least three times. Not right now, of course, as you are under time pressure, but as a general comment.

    In the long run, this helps you to keep a flexible data structure, because you know and have tested at least some alternatives. I'm sometimes surprised on how little the impact of a fundamental structural change is on the user interface.

    Anyway, good luck with your 60 tables, and don't hesitate to play with them on scratch copies of the database.


    Author Comment

    no i didnt expect you to do it for me or anything... just wondered if you had any recomendations as far as structure was concerned... but you are right playing around is the best option... and if i ever do an overhaul the users wont experience any problems because all it will entail is an update of the front end and backend which i can replace when my new version is ready... i really appreciate the comments and help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now