Not able to only add records with OpenRecordset (1 Viewer)

cage4000

Registered User.
Local time
Today, 16:14
Joined
Oct 14, 2015
Messages
49
Good afternoon you amazing Access geniusis!

I've ran into a problem with trying to only add a record and not bring up the entire table with my Access Form using a recordset as the connection to the SQL Server. All i want to do is add records to the table. can anyone help me with this.

here is the code i used:

Code:
Dim sqlMain As String
   Dim rsMain As DAO.Recordset
    
    sqlMain = "SELECT * FROM tblPRF"
        
    Set rsMain = sqlDB().OpenRecordset(sqlMain, dbOpenDynaset, dbAppendOnly)
    
    Set Me.Recordset = rsMain

it brings all records up when i change out the ",dbAppendOnly" to ",dbSeeChanges" but i only want to append records to the table.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 28, 2001
Messages
27,001
It appears that you are replacing the recordset of a form with a SELECT statement. If that form is then opened, what you described is what I would expect for you to see.

This sounds like a design/concept problem. I'm sure some of our other members will be able to guide you better, since I don't know as much about SQL Server as I do about some other products.

I have to ask, what is your level of expertise in Access? Because we will know better how to tailor our answers if we have an idea of your abilities.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:14
Joined
Apr 27, 2015
Messages
6,286
Have you tried simply using your form in Data Entry mode?
 

cage4000

Registered User.
Local time
Today, 16:14
Joined
Oct 14, 2015
Messages
49
My level is about average to above average. I tried it with the entry mode set to yes but it had no affect. it looks like my recordset is unaffected by my forms controls it simply populates the data in it.
 

Mark_

Longboard on the internet
Local time
Today, 16:14
Joined
Sep 12, 2017
Messages
2,111
Have you tried creating a query to return the top 1 record? If you only look at the LAST record in you query but add to it, you should be able to avoid dragging all records over OR letting your end users see them.
 

cage4000

Registered User.
Local time
Today, 16:14
Joined
Oct 14, 2015
Messages
49
That is a great idea, but i was massing with it some more and i found that if i tell it the SQL statement to null out the key it will not produce records but still pull it up so i can add records. thank you all for your input, i think i figured it out.

this is what i changed it to:

Code:
   Dim sqlMain As String
   Dim rsMain As DAO.Recordset
    
    sqlMain = "SELECT * FROM tblPRF Where PRF_ID is null"
        
    Set rsMain = sqlDB().OpenRecordset(sqlMain, dbOpenDynaset, dbAppendOnly)
    
    Set Me.Recordset = rsMain
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:14
Joined
Feb 19, 2002
Messages
42,977
Access is a Rapid Application Development tool. At its heart is bound forms. If you are not going to use bound forms, you are getting little benefit from using Access and still have all the bad stuff. You would be far better off using a different platform.

I've been using Access as a front end to ODBC databases such as Oracle, DB2, SQL Server, Sybase, and several others since I first discovered Access in the early 90's. In fact that ability to interact with DB2 was EXACTLY what prompted me to adopt Access to begin with. Although there are times when I might build views or stored procedures or use pass through queries, the interactive part of the app revolves around bound forms. The trick is understanding how Access works so you can optimize the data retreival from SQL Server. As someone already mentioned, Select * with no criteria is going to bring down every single record in the server side table. Not exactly what you are aiming for.

I always use forms that do both add and update because otherwise I would have to duplicate the validation and that is a recipe for disaster. The key is to limit the data returned by the form's RecordSource so I use a query and that query takes at least one argument. Frequently the only search is a combo or text box on the main form. So when the form loads, that textbox or combo is empty so no rows are returned. Then in the AfterUpdate event of the textbox or combo, I requery the form and now when the query is sent to the server, it includes criteria that will select one record. So limit your main forms to a single record if at all possible. The subforms will be filtered based on the record shown in the main form.

If some joins are slow, try building views. If you have to do bulk deletes, use pass through queries and if you are deleting all rows, use Truncate rather than delete *. Reports can sometimes require complicated stored procedures depending on how much data they are consolidating.

The only issue with bound forms is if you are trying to connect Access to a remote database using the internet. Access is optimized to work on a LAN. Running it on a WAN is painfully slow. Even the fastest internet connection is barely a tenth of the speed of a moderate LAN. I have never actually had any success getting an app to work well over the internet. Others have but they have better service providers or host the azure database themselves so they have sufficient speed and bandwidth. Just getting a cheap plan from a service provider simply doesn't cut it.
 

missinglinq

AWF VIP
Local time
Today, 19:14
Joined
Jun 20, 2003
Messages
6,423
I totally agree with Hartman! Using Unbound Forms really does away with the basic function of Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t! You don't need Unbound Forms to

  1. Do Data Validation
  2. Prevent Duplicate Records
  3. Do Formatting of Data before it's Saved
  4. Decide whether or not to actually Save a New or Edited Record

which are the most common reasons given. Nor are they needed for another dozen reasons I've seen people give!

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything...even the most mundane tasks!

Bottom line is…with Bound Forms you end up writing code for a few specialized situations, such as #1-#4, as listed above…and with Unbound Forms you have to write code for virtually everything that needs to be done!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

  • You can create an EXE file which gives total protection to your code/design
  • You can distribute the db to PCs without a copy of Access being on board
  • Your data security is far, far better than anything you can do in Access

Don't misunderstand me...there are a few, specialized situations, where an Unbound Form is preferable...but anyone who routinely uses them for everything, has simply made a bad choice in deciding to work in Access.

Linq ;0)>
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:14
Joined
Apr 27, 2015
Messages
6,286
I was under the impression the OP was in fact using a bound form, which is WHY I suggested Data Entry mode.

Maybe I should take a reading comprehension course...
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 10:14
Joined
Jan 20, 2009
Messages
12,849
I was under the impression the OP was in fact using a bound form, which is WHY I suggested Data Entry mode.

Maybe I should take a reading comprehension course...

The OP is using a bound form. It is bound by setting the Recordset Property rather than RecordSource Property. Still bound and works exactly the same either way.
 

Mark_

Longboard on the internet
Local time
Today, 16:14
Joined
Sep 12, 2017
Messages
2,111
I also figured they were using a bound form but didn't want end users to access existing records.

From a business standpoint I can definitely understand allowing some users to ONLY enter data and not view/change/delete anything.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:14
Joined
Feb 19, 2002
Messages
42,977
If you want to use bound forms, simply specify the name of a table or query as the RecordSource. If you are using the same form for both add and update (strongly recommended) then you should be using a query with selection criteria. That way the form will open empty and ready for data entry. Most of my main edit forms have one or more combos or textboxes in the header that can be used for locating a record. The RecordSource query references these controls so when the form opens, the controls are empty and therefore the form loads no records. In the AfterUpdate event of the search control (if multiple controls, i use a button instead), you requery the form to get Access to send the query with the argument to the server so it can retrieve the desired record.

If you want the user to only be able to add records, you can open the form in DataEntry mode. However, this does not prevent the user from updating records he added during this session since he can scroll forward and backward though the open recordset and it will contain all records added since the user opened the form. Closing the form and reopening it reverts to an empty recordset.

If you don't want people to update records after they have been saved, You can add code to the on Dirty event. Check to see if this is a new record. If it is, undo the change and give the user an error message explaining that he cannot change a record once it has been saved.
 

Users who are viewing this thread

Top Bottom