Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Using VBA to Run SQL (Updating Records)

    I'm trying to update a table in a 2003 Access database from a dbo in another similar database. I'd like to accomplish this without linking to the table in the second database b/c it's very large and has a negative effect on efficiency. I'm only updating a single data column in my first/working database. I've studied the SQL from my update query (with the second table linked) and spent a fair amount of time searching threads to no avail. I'm hoping someone might be able to assist. Below is the SQL from my update query and the file path to the second database. I presume the best way to do this is with VBA and the RunSQL command.


    UPDATE COMPReviewMain LEFT JOIN [Job Description Master File] ON COMPReviewMain.[Position Code] = [Job Description Master File].[Job Code] SET COMPReviewMain.[Job Description(Hyperlink2)] = [Job Description Master File]![Path 1]
    WHERE ((([Job Description Master File].[Path 1]) Is Not Null));



    \\xxxx01\HR-Shared\Job Descriptions\Job Description Lookup\JobDescriptionLookup.mdb

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If the db is very large and efficiency is an issue, have you consider using SQL Server/SQL Server Express?

    Using SQL Server or SQL Server Express (which is free), you could design a trigger to update the data in the 2nd database without even worrying about linking the table into the mdb. You'll also find using SQL Server tables will be beneficial all around (ie. you can setup a good backup maintenance plan which backs up the db every night and also set up a Translog backup plan for point-in-time restores). I have a few SQL Server db's (which are about 100+ megs) and backing up the tranlog every hour takes less than a second (the user doesn't even notice it happening.) I even had to restore one of the dbs at a specific point in time a few times and it works extremely nice (ie. the user accidently updated several thousand records at noon one day and needed it restored from 10:00 am that morning.) It was extremely easy to restore the nightly backup and the tranlogs up to to 10:00 am - just a few clicks.

    Triggers also work very well with SQL Server and you can design them so if a specific field is updated in one db, it automatically updates another field in a different db.

    Just a recommendation.
    Last edited by pkstormy; 09-01-07 at 23:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    something like the following DAO code should do the trick (early-bind = you need a reference to DAO):

    dim oExtDB as DAO.database
    dim oQdef as DAO.querydef
    dim strSQL as string
    dim strExtDB as string
    strExtDB = "x:\here\there\theRemoteDb.mdb"
    strSQL = "UPDATE tblThis SET strThat = 'this was remotely updated'"
    set oExtDB = opendatabase(strExtDB)
    set oQdef = oExtDB.createquerydef("", strSQL)
    oQdef.execute
    set oQdef = nothing
    oExtDB.close
    set oExtDB = nothing

    it is a relatively dangerous thing to do - take great care not to trash your data.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Can you help me understand the first part "(early-bind = you need a reference to DAO):"? I'll need to update my main table with data from the second database immediately after a user opens the primary database. I figured any code could be activated with an event property like when the main form opens at startup. So, would I place this DAO code under an "On Open" event or does it need to be in a separate module? I'm not a professional programmer but definitely want to give this a try.

    Secondly, was your thinking to plug in the SQL code that I initially attached, as is? It looks like that might belong in this line:
    strSQL = "UPDATE tblThis SET strThat = 'this was remotely updated'"

    or this line:
    set oQdef = oExtDB.createquerydef("", strSQL)

    I wasn't quite sure. I'll definitley backup my database before trying this!

    Finally, the first suggestion definitely sounds good to me, however, I neglected to mention that the second or other database does not belong to me, it's in another area of the company and at another location. I simply need to get one column of data from it on a frequent basis and as efficiently as possible. Thank you though for taking the time to respond.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    wrong direction

    OOOOOOPS!
    I simply need to get one column of data from it on a frequent basis and as efficiently as possible.
    my fault, i thought you wanted to update the remote data, but now i see you want to update your local data based on some field in the remote data. i don't think my code is going to help you.

    to answer your peripheral questions:

    Can you help me understand the first part "(early-bind
    it is a question of when Access tries to understand what you want to do: early-bind happens at compile-time (+/- as you type the code), late-bind schemes delay the 'understanding' (+/- each time the line of code executes).

    my early-bind DAO code example needs an Access reference set to Microsoft DAO (normal in most Office versions but not 2000) so that the Access "compiler" can understand what is DAO. late-bind can live without the reference and will either run faster or slower than early-bind (surprisingly difficult to predict!). late-bind is much more version-independent.

    it is easier for me to tell you to make a reference to DAO and use early-bind than to explain the late-bind equivalent code.


    It looks like that might belong in this line:
    strSQL = "UPDATE tblThis SET strThat = 'this was remotely updated'"

    exactly! if you had working SQL and my proposed method was suited to your task, that is where your SQL would go.

    or this line:
    not my original intention (but sure, you could forget strSQL and have literal SQL there)


    it seems to me that you are going to need a JOIN to solve your update and for the moment i just don't see how to do that without linking.

    sorry about suggesting a wrong direction.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Thanks for the update. I may need to try another direction. It's too bad there isn't a way to link the dbo with only the single column of data that I need. Part of the problem is the large size of the table in the remote database and part of the problem is no doubt bringing the data accross our wide area network. Even attempting an update via SQL may not have helped. Thanks again.

  7. #7
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    The Best way for Updating Large data is using SQL Update Query, It is Fast and reliable.
    If you have problem with writing downe the Update SQL Query then Simply make an Update Query in your db and then open it in SQL view which show you the SQL string then you can modify it to what you need.
    Cheers

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Just out of curiosity, have you run any speed tests with the large table linked into the mdb verses not having the large table linked into the mdb? I mean if you were to link the large table into a copy of the mdb and test that do you suddenly find that your forms in the mdb are taking longer to load or something else working slower by just having the large linked table in the mdb? Location of where the mdb's are located also makes a difference on performance (ie..linking in a table from an mdb on a slow networked system.) If I move the large mdb to a faster networked drive I don't have issues. There are also many other factors which can affect the performance of an mdb.
    Last edited by pkstormy; 09-05-07 at 11:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    To answer you question, both queries and forms (unrelated) take longer to open when the remote table is linked. My primary database resides on a large file server and operates very efficiently without the linked, remote Access table. The drain on operating efficiency is probably attributable to both table size and bandwidth on the WAN.

    I haven't given up on at least testing an alternative approach - using SQL with VBA to update my primary table. I've been reading about Microsoft Access SQL's "IN" clause which, by specifying a database name (path to the file or directory) and connect string (database type), allows a user to connect to a database outside the one that's currently open. If I can make use of this with a correctly written update statement in SQL, I should be able to get the data from the remote Access database to my primary database each time it's opened without attaching the table. I've tried studying my working update query in the SQL view of the expression builder but the syntax is obviously different in raw SQL. If I can figure this out, I'll update 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
  •