[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Access VBA SQL error

Posted on 2011-09-15
Medium Priority
Last Modified: 2012-05-12
Using this code but get an error (See below)
Sql = "SELECT tbl_Inventory.InternalKeyNumber "
Sql = Sql & "FROM tbl_Inventory "
Sql = Sql & "WHERE tbl_Inventory.InternalKeyNumber = " & Ser

Set rst = Dbs.OpenRecordset(Sql)
    If rst.RecordCount = 0 Then
    MsgBox "Invalid Serial Number. Please try again"
    Exit Sub
    End If

 "You mus use dbseechanges with openrecordset when accessing SQL server table that has an identity column"
Question by:HKFuey
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 61

Expert Comment

ID: 36542153
Use this instead:

Set rst = db.OpenRecordset(sQL,dbOpenDynaset, dbSeeChanges)
LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 36542155

Set rst = dbs.OpenRecordset(sQL,dbOpenDynaset, dbSeeChanges)

Author Comment

ID: 36542163
Hi mbizup

I get "Object Required error"
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


Author Comment

ID: 36542166
I got it thanks
needs to be Dbs!!
LVL 61

Expert Comment

ID: 36542169
Do you have DBS defined anywhere?

Dim dbs as Database
Set dbs = CurrentDB
LVL 61

Expert Comment

ID: 36542172

Author Closing Comment

ID: 36542174
Superb! thanks!!!

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question