?
Solved

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

Posted on 2007-07-24
9
Medium Priority
?
253 Views
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.
0
Comment
Question by:jtovar3
  • 4
  • 4
9 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 19559515
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:

   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstdatatypeenum.asp

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.Refresh
      .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
      MyRecordset.MoveNext
   Wend

   ' 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
      Next
      .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"
   MyRecordset.Update

   ' 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.AddNew
   MyRecordset!Field1 = "Some data"
   MyRecordset.Update
   
   ' Delete current record
   MyRecordset.Delete

   ' Close recordset
   MyRecordset.Close

   ' Close database
   MyDatabase.Close

Kevin
0
 
LVL 58

Expert Comment

by:harfang
ID: 19559981
Woot, zorvek! This looks like several hours of work...

jtovar3,

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.

(°v°)
0
 

Author Comment

by:jtovar3
ID: 19568852
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?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 58

Expert Comment

by:harfang
ID: 19568949
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!
(°v°)
0
 

Author Comment

by:jtovar3
ID: 19569034
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
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 19569278
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
        cboSelectTable.SetFocus
        cboSelectTable.DropDown
    Else
        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.

(°v°)
0
 

Author Comment

by:jtovar3
ID: 19569408
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
0
 
LVL 58

Expert Comment

by:harfang
ID: 19569557
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.

(°v°)
0
 

Author Comment

by:jtovar3
ID: 19569590
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

864 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