View original (French)

VBA Access to records of a linked table

Audrey -  
adelavar Posted messages 4 Status Member -
Currently, I am working on Access. My VB program works with tables in the same database, but if I link them, it no longer works.
Indeed, I want to access the records of my database with a recordset, but the method seems to be different with linked tables. How should I proceed?
Thank you in advance.

10 answers

  1.
    adelavar Posted messages 4 Status Member 2
     
    Thank you both, it was indeed the dbopendynaset action that needed to be used.
    Access to the records doesn't even deserve to be changed, it works like that.
    2
  2. blux Posted messages 2061 Registration date   Status Moderator Last intervention   3 455
     
    Hello,

    what do you mean by linked tables, are they attached tables?
    if that’s the case, it shouldn't be a problem. I’ve been using it for a long time and I’ve never had an issue.

    Maybe they are linked tables with ODBC?

    Can you tell us more: error message, application behavior?

    See you Blux "Fools will do anything.
    That’s how you recognize them."
    0
  3. teebo Posted messages 33585 Registration date   Status Contributor Last intervention   1 797
     
    VB or VBA too...

    .  .
    \_/
    0
  4. teebo Posted messages 33585 Registration date   Status Contributor Last intervention   1 797
     
    And are you sure about the connection in question?

    .  .
    \_/
    0
  6. adelavar Posted messages 4 Status Member 2
     
    Yes, these are linked tables.
    Let me clarify:
    I have a database
    and a processing database. IN THIS DATABASE, I GO TO FILE - External Data - Linked Tables, and there my tables are linked in my processes (for your information, this allows the user to continue working and filling the database while we modify states or forms...).

    So with linked tables, Recordset does not work; there is something else to do.

    Thank you for your attention.
    0
  7. blux Posted messages 2061 Registration date   Status Moderator Last intervention   3 455
     
    what is the error message? what's happening? because "recordset does not work, there's something more to do" is not an ACCESS error message (I've checked :-))

    when you go to Tools/Add-ins/Linked Table Manager, do you see your tables with the location of the source tables or do you have anomalies?

    See you Blux "Fools dare to do anything.
    It's even how we recognize them."
    0
  8. adelavar Posted messages 4 Status Member 2
     
    Execution error 3219
    Debugging
    Set MaTable = MaDB.OpenRecordset("TEnregist", DB_OPEN_TABLE)

    Is that error good for you?
    0
  9. blux Posted messages 2061 Registration date   Status Moderator Last intervention   3 455
     
    It's clearer now! :-)

    Did you do:

    Set MaDB=Currentdb? (or something else)
    Are MaDB and MaTable objects of type database and recordset, respectively?

    See you later Blux "Fools dare to do anything.
    That's even how we recognize them"
    0
    1. TopperHarley Posted messages 8 Status Member
       
      Hello everyone,

      no need to use "set MaDB = currentdb" because he wrote:
      Set MaTable = MaDB.OpenRecordset("TEnregist", DB_OPEN_TABLE)
      But you can use linked tables in VBA thanks to the TableDefs collection.
      The TableDef object can also manage links to ISAM and ODBC data source tables. You need to include the Connect and SourceTableName properties in your code and the CreateTableDef method for this activity.
      You call the CreateTableDef method to define a reference to the linked table in a variable. Then, you set the Connect and SourceTableName properties of the variable. The Connect property specifies the type of data source and the path to the specific data source you want to link.
      SourceTableName is the name of the table you are linking.
      Finally, you need to complete the process by adding the TableDef object to the TableDefs collection.

      Happy coding...

      Guillaume, at your service and incidentally helping me too
      0
    2. blux Posted messages 2061 Registration date   Status Moderator Last intervention   3 455 > TopperHarley Posted messages 8 Status Member
       
      Yes, but what is the value of MaDB? We don’t see it anywhere, and we refer to it in OPENRECORDSET.

      I'd like to know why you say there is no need to set ... = currentdb because I don’t see how this action in openrecordset knows which database it’s referring to.

      Later, Blux "Idiots dare to do anything.
      That’s how you recognize them."
      0
    3. TopperHarley Posted messages 8 Status Member > blux Posted messages 2061 Registration date   Status Moderator Last intervention  
       
      Hello to you,

      yes, you are right, sorry for omitting to mention all that.
      So here’s an example to satisfy you, no need to specify the base for the linked tables (unless you're using elements from it):
      dim rs as recordset
      set rs = new ADODB.recordset
      rs.open "clients",_
      "provider=microsoft.jet.oledb.4.0;"&
      "Data source=C:\program files\microsoft office\office\"&_
      "Samples\comptoir.mdb;"
      .
      .
      .
      rs.close
      end sub

      So happy?!? lol

      Guillaume, at your service and incidentally helping me too.
      0
    4. blux Posted messages 2061 Registration date   Status Moderator Last intervention   3 455 > blux Posted messages 2061 Registration date   Status Moderator Last intervention  
       
      Hi,

      thank you for the examples, but I'm not the requester! :-)

      Audrey's initial request is based on tables that are already linked, there's no need to redefine the links.

      I just want her to confirm the values of MaDB and the declaration of the objects (database and recordset)... :-)

      See you, Blux "Fools dare to do anything.
      It's even how you recognize them."
      0
    5. TopperHarley Posted messages 8 Status Member > blux Posted messages 2061 Registration date   Status Moderator Last intervention  
       
      Earlier, I provided the solution to Audrey; the tables are already linked, yes, but a connection needs to be established in VBA to fetch those data. It's just like with a file, before reading the information, you have to open the file! lol
      As for Audrey, it would be great if she could confirm whether her issue is resolved to avoid revisiting the subject all the time, but I believe I answered correctly.
      Between the two of us, the solution should work!

      Guillaume, at your service and occasionally helping me too
      0
  10. blux Posted messages 2061 Registration date   Status Moderator Last intervention   3 455
     
    I found it!!!

    In:
    Set MaTable = MaDB.OpenRecordset("TEnregist", dbOpenTable)

    DB_OPEN_TABLE does not exist, it's "dbOpenTable" that needs to be used, I was thinking that there was something visually off!

    And there you go! :-)

    See you, Blux "The fools dare everything.
    It's even how we recognize them"
    0
  11. adelavar Posted messages 4 Status Member 2
     
    I just got back.. I’ll take the time to analyze everything you've put and test it, and I’ll let you know what it yields.
    Thank you.
    0