Results 1 to 11 of 11
  1. #1
    sgroenen is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Location
    Hamont-Achel
    Posts
    6

    open form even database or query empty

    I have a database with many tables. I use ADO to show all to my form. This for safety reason because not directly put all in table and many people working on this database. To open form ther must run a query. But if the queary not give table I get an error. So how best test this so he not go to connect all fields to my txt fields on my form. I can't find any solution for this. Any examples will help to build this.

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you don't need to use ADO. All tables are in the db and can be used in a query, and show only certain fields in the query.
    just open the query: docmd.openquery "qsMyquery"
    users can use a combo box to pick the query.

    hide the tables/queries from the users.
    you also want a split database if many users are in it. Each user gets their own front end copy.

  3. #3
    sgroenen is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Location
    Hamont-Achel
    Posts
    6
    ADO is needed because many person work same time on this table so we get problems

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That's the wrong solution to managing issues with multiple users of a database.
    At some point you WILL get serious corruption and your database may become unusable with all data lost.

    Instead split the database with all tables stored in a shared backend stored on the network
    The frontend should contain all other objects together with links to the backend tables. Each user needs their own copy of the frontend on their own workstation
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    sgroenen is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Location
    Hamont-Achel
    Posts
    6
    Quote Originally Posted by isladogs View Post
    That's the wrong solution to managing issues with multiple users of a database.
    At some point you WILL get serious corruption and your database may become unusable with all data lost.

    Instead split the database with all tables stored in a shared backend stored on the network
    The frontend should contain all other objects together with links to the backend tables. Each user needs their own copy of the frontend on their own workstation
    For don't get corruption I use ADO. Then when they fill in fields they not working direct in table. They must first pusch save before everything is send to database. That I have already lookup in many books. But my problem now is that the query can give an empty table and then program give error. Normaly this not happen but when first start tables can empty. For preventing this I just ask how to get fix this. So when no table he must open my form but with nothing

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If you changed to the standard approach of a split database, it wouldn't require the use of ADO and so wouldn't have this error
    Instead you are using an unnecessarily complex method which errors in certain circumstances.

    Sorry but in that case I can't help you. Good luck with your project
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    really don't know how you can expect an informed answer if you don't provide some information such as the code you are using and where you are using it.

    in your first post you say
    to open form ther must run a query. But if the queary not give table I get an error.
    what error? what is the description, what line of code is highlighted?

    Also confirm your db is split with each user having their own copy of the front end. ADO or not, you will get corruption if not.

    Using (I presume disconnected) ADO recordsets for new entries is one thing. Using them for editing existing data is fraught with problems due to the lack of record locking. For the DAO equivalent of a disconnected ADO recordset you would use begintrans and committrans which gives you much more control when attempting to update several tables at the same time (such as invoice header and invoice lines)

    Best I can suggest is test for an empty? missing? table? field value? before trying to save to the database

  8. #8
    sgroenen is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Location
    Hamont-Achel
    Posts
    6
    Here is the code I use for the moment
    Option Compare Database
    Option Explicit
    Dim sspcnn As ADODB.Connection
    Dim sspcmd As ADODB.Command
    Dim ssprst As ADODB.Recordset
    Private Sub Form_Open(Cancel As Integer)

    'leegmaken van de bestringselementen
    ' vergrendelen van de besturingselementen
    ' opdrachtknoppen in- of uitschakelen
    leegmaken
    vergrendelen
    ' Openen verbinding
    Set sspcnn = New ADODB.Connection
    sspcnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=R:\ssp.accdb"
    'Command objecten creeeren
    Set sspcmd = New ADODB.Command
    sspcmd.ActiveConnection = sspcnn
    sspcmd.CommandType = adCmdStoredProc
    sspcmd.CommandText = "qryssp_FM"
    'opdracht uitvoeren en recordset maken
    sspcmd.Execute
    Set ssprst = New ADODB.Recordset
    ssprst.Open sspcmd
    'opvullen van de besturingselementen
    txtBestandsnaam = ssprst.Fields("Bestandsnaam").Value
    txtOmschrijving = ssprst.Fields("Omschrijving").Value
    txtNieuweversie = ssprst.Fields("VersieSSP").Value
    txtHuidigeversie = ssprst.Fields("OudeVersie").Value
    txtDatum = ssprst.Fields("Datum").Value
    End Sub
    It fails on this line
    txtBestandsnaam = ssprst.Fields("Bestandsnaam").Value
    when query is empty. This only go happebn one time when table is just build for first time

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you still have not provided the error message, however looks like you need to count the number of records returned and if zero, not proceed.

    Code:
    if ssprst.recordcount>0 then 'continue
    
    else
        msgbox "no records"
    
    end if
    you may have to movelast in order to get the recordcount, otherwise it might return -1

    however you are not disconnecting the recordset. All you have is something which is effectively the same as DAO but with less flexibility and more complexity. It certainly won't prevent corruption if your db is not split and/or all users are using the same front end.


    As with Colin, this is the wrong way to go so I'll leave you to continue in that direction. Good luck with your project

  10. #10
    sgroenen is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Location
    Hamont-Achel
    Posts
    6
    Like I say this is not complete because I do not do now a disconnection. And in all books and learning they say ADO is more then DAO. Microsoft was ending DAO and in Visual Basic you can use also ADO what is very flexible. The other thing I can do now also have in programming the control what can post. But with this solution I can handle this error. Thanks

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Microsoft was ending DAO
    that was 20 years ago, they tried ADO for a couple of years then reverted to DAO.

    Note that if you are using ADO recordsets for continuous forms, the standard filter and sort functionality will not work, you will need to write your own alternatives.

    And in all books and learning they say ADO is more then DAO.
    would be interested in some links to recent articles/books that confirm this.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-20-2017, 02:51 PM
  2. Replies: 2
    Last Post: 06-25-2014, 10:29 PM
  3. Replies: 1
    Last Post: 03-02-2014, 01:31 PM
  4. Replies: 4
    Last Post: 03-01-2012, 08:15 PM
  5. Replies: 4
    Last Post: 11-20-2011, 01:08 PM

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