Results 1 to 4 of 4
  1. #1
    mhaskell is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Indiana
    Posts
    2

    Import and Update from ODBC connection to Access Tables

    I'm using basis bbj odbc connection to access a database remotely for my office. The ODBC connection works great and I can create my queries within access.
    However I have not figured out how to create a script or some way to update the tables without deleting the table and completely reimporting it.
    I'm using some very simple asp classic web pages to display the data and it will not work if I use a linked tables. However if I import the data and make it local, then the pages work great.


    My preferred method would be to have a script to update the table automatically once a day. Anyone with an idea on how I would go about this?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Duplicate the tables and update/append via queries linked to the odbc tables? If you're only using some of the fields from the odbc side, start with a make table query and have the native table only contain the fields you need.

    As for daily updating, my preferred method was to use windows task scheduler. I had it open the db using a command line switch in a shortcut and the update code knew if it was a user or pc that was opening the db. If pc, code execution was direct and didn't present any messages that couldn't be dealt with, or worry about anyone being logged in because the update ran at 4:00 AM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    mhaskell is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Location
    Indiana
    Posts
    2
    I have never updated a table from a query. I usually update the table from a form. I'm a little confused on how to do this. Can you reference me to any documents that may help. I understand the theory, I'm not sure how to execute.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A simple SELECT query is usually updatable simply by changing data in datasheet view, which might be fine for minor edits. One still might do this via a form since a form can be based on a table or query. Sounds to me like you're saying you've always based a form on a table up to now. For mass edits/updates or to automate such updates, you use either an UPDATE or APPEND query. An update can have static values in the UPDATE TO row in query design grid, or you can link to another table/query and have the update query use values from those fields (UPDATE TO [tblMyTable].[MyField]). Usually you need a WHERE clause with this in order to use the correct values from the second table (such as WHERE tblMyTable.SomeID = tblSecondTable.SomeID), or the join(s) between fields must be able to limit the records based on them having equal values.

    When you consider a query as the basis for a form or report to be pretty much the same as basing them on tables, it's not too hard to grasp, I think. There are situations where a query isn't updatable directly (as in typing something in a field in datasheet view) and many such reasons are shown here http://allenbrowne.com/ser-61.html
    However, for your case I think the need is to run a query to do many updates or appends in one operation as mentioned above. I thought your need was to use make table queries though, to make tables out of your csv files. Perhaps you need to do both eventually.

    Google MS Access query types and do some research on these query types if you're still unsure of them.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-25-2018, 10:32 AM
  2. Schedule daily overnight import or refresh with ODBC connection
    By kagoodwin13 in forum Import/Export Data
    Replies: 11
    Last Post: 11-13-2015, 11:37 AM
  3. Replies: 3
    Last Post: 04-24-2015, 05:09 AM
  4. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  5. Unable to import or link tables through odbc in Access SP2
    By Dave Jenkins in forum Import/Export Data
    Replies: 3
    Last Post: 11-09-2005, 11:51 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums