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

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.
Who is Participating?
harfangConnect With a Mentor Commented:
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.

zorvek (Kevin Jones)ConsultantCommented:
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:
   '   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
   '   and navigate to Open Method (ADO Connection)
   '   Additional help constructing connection strings can be found at http://www.connectionstrings.com/

   ' 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:
      '   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
      '   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:
   '   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadomethods.asp
   '   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

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.

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

jtovar3Author Commented:
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?
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!
jtovar3Author Commented:
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
jtovar3Author Commented:
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
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.

jtovar3Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.