Solved

Access VBA SQL error

Posted on 2011-09-15
7
352 Views
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

ERROR:
 "You mus use dbseechanges with openrecordset when accessing SQL server table that has an identity column"
0
Comment
Question by:HKFuey
[X]
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
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36542153
Use this instead:

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

Accepted Solution

by:
mbizup earned 500 total points
ID: 36542155
Sorry...

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

Author Comment

by:HKFuey
ID: 36542163
Hi mbizup

I get "Object Required error"
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

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

Expert Comment

by:mbizup
ID: 36542169
Do you have DBS defined anywhere?

Dim dbs as Database
Set dbs = CurrentDB
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36542172
:-)
0
 

Author Closing Comment

by:HKFuey
ID: 36542174
Superb! thanks!!!
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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