Solved

Check duplicate entries in access 2007 vba, sql 2008

Posted on 2011-09-17
21
393 Views
Last Modified: 2012-05-12
HI have an app in access 2007, trying to modify the code to connect it to sql 2008.

I have a form with a subform.

The employeeID is in the form and Subform has employee's tasks. the relationship is from form to subform one to many.(employee table  to task table)

initially application was not connected to sql server and code below was used:
there is a code which checks if a task has already been asigned to the employee and if it is generate message.
private sub cmb_task_beforeupdate(cancel as integer)
  dim rst recordset
  dim sql as string
  dim ind as integer

  sql = select taskxxx.[employeeid] from task where (((taskxxx.[employeeid])=" &parent![employeeid] & ") and (( taskxxx.[taskid]) ='" & me.cmb_task & "'));"
  set rst = currentdb.openrecordset(sql,dbopensnapshot)
  ind = rst.recordCount
  if ind > 0 then
    msgbox "duplicate"
    cancel = 1
  end if

Open in new window



now that I am connected to sql server I changed the sql = statement above to:
sql = select task.employeeid,task.id from task where task.employeeid = " & parent![parent_id] & ") and (( task.id)='" & me.cmb_task & "'));"

Open in new window


I am not familiar with access snapshot,.... all I need is to check if select statement returns any value and then cancel addition of tasks.
how can I fix above code in vba?

thanks
0
Comment
Question by:shmz
  • 9
  • 5
  • 5
  • +1
21 Comments
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Well, if using DAO, then think it should be fairly straightforward (famous last words).

Seems that your query is not quite correct, and what you want to achieve is pretty much the unbolded area, but seems to be a few challenges with brackets and maybe datatypes (you enclose me.cmb_task in single quotes).

So, what about...

    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ind As integer  
    
    SQL = "select task.employeeid,task.id from task where task.employeeid = " & parent![parent_id] & " and task.id ='" & me.cmb_task & "'"
        
    Set rs = CurrentDb.OpenRecordset(SQL)

    ind = rs.recordCount

etc...

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Oh, and you should properly qualify me.cmb_task. In fact go into debug mode and make sure those values are what you think they should have.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
There was a problem with unbalanced parens.  Please try the following.

sql = "select task.employeeid, task.id from task where (task.employeeid = " & parent![parent_id] & ") and (task.id='" & me.cmb_task & "');"

Open in new window


==============
If you are just looking for existence, I don't see the need to return any data from the task table.
private sub cmb_task_beforeupdate(cancel as integer)
  dim rst recordset
  dim sql as string
  dim ind as integer

  sql = "select 1 from task where Exists (Select * From task As X " & _ 
           "Where X.employeeid = " & parent![parent_id] & ") " & _
           "and (X.id='" & me.cmb_task & "')"

  set rst = currentdb.openrecordset(sql,dbopensnapshot)
  
  If rst.EOF then
  Else
    Msgbox "duplicate"
    Cancel = 1
  End If

Open in new window


Not knowing your run time environment (DB configuration), there might be other approaches to this problem, such as the DLookup() function or other functions that might optimize this.

Rather than doing such a duplicate check call, it might be simpler to add a unique index to your tasks table (employeeid and id columns).  That way, your Insert statements would be prevented from adding duplicate rows.
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
An alternative would be to use a Stored Procedure on the SQL Server database and pass it the 2 parameters as well as adding the unique index on the target table.  The SP could handle the insertion and then pass back either "Task already assigned to this Employee." if the result is a Duplicate Key error or "Task has been assigned to this Employee." if the INSERT succeeds.  

That should let you assign the Results from the SP ccall to a variable that you simply display in a message bow.
0
 

Author Comment

by:shmz
Comment Utility
Thanks Driver8080
using stored procedure would be my preference but what should be the syntax, do I need to open the connection and so on or just place EXEC command within the code you provided?
0
 

Author Comment

by:shmz
Comment Utility
I am getting error on set rst..
rst return nothing
error is 'object variable or with block variable is not set...

the code I provided was initially connected to access and now i am trying to connect it to sql server
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Is the SQL Server version of the tasks table an attached table to the Access database?
0
 

Author Comment

by:shmz
Comment Utility
There is no table in access. all tables are in sql.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
is the table attached?
0
 

Author Comment

by:shmz
Comment Utility
I am connected to sql server  as far as I know which is the database I have created and has nothing to do with access. I then selected my database to be connected to my access application.

Does this answer your question?

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
do you see anything in the list of tables?
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
0
 

Author Comment

by:shmz
Comment Utility
Driver8080
Since I connected the application to my sql server database, I only see list of my tables which are different from the old tables they had.

mark_wills
I do not want to do anything with access database.
I need to fix preexisting access app to work with sql hence I need to correct the code.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@shmz

>>I do not want to do anything with access database.

1. Your code will execute in an Access database
2. Without knowledge of your environment, we have no way to solve your problem that would involve direct access to SQL Server from your code.

Please attach the SQL Server table to your database.  There are plenty of options for a solution configuration, but we're in the dark right now.
0
 

Author Comment

by:shmz
Comment Utility
This is scary!
I am creating stored procedure using access interface and they are being saved in sql server under programability folder as well.
I also see them under the list of Query tab in access interface.

I deleted some of the stored procs from sql server and they are no under the query tab in access interface.

The app was previously creaed for access database but it is no longer connected to any access table.
that is perhaps why I am getting error messages as the app is connected to sql for sure.

am I very wrong?
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
No, not wrong... Just a bit confused with having SQL Server as a backend.

Being way too brief (and consequently not 100% accurate)... But an overview of using SQL with Access may read something like :

Access is both a programming language for forms and reports (and more) and a data repository. Having linked tables simply means they are pointing back to the original datasource, but you can refer to them locally (similar to what you see with those stored procedures). Your forms and reports (and macros) are local to your access database.

If not, then (worse case) you could end up coding every get and put with your own code and establishing a connection string for the SQL Server and incorporating that connection string in every read / write. You would have tons of macros. Essentially you end up using unbound forms.

In your Access database, do you see a list of tables ? do you see a lot of macros ? Is it an ADP ?

To help your understanding, have a quick read of these : http://searchsqlserver.techtarget.com/tip/Microsoft-Access-2007-integration-with-SQL-Server

And some great advice / examples here : http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp (check out the "optimising" one it explains a bit).
0
 

Author Comment

by:shmz
Comment Utility
The access app was given to me with bunch of non related tables and it was already connected to sql server.

I connected the access app to a new sql db I created and since I have done that, under the table tab of access,II see the name of tables which I have created in the new database only. The previous names are not there.

I do not see any macro. However sometimes when I change the code, I get some access warning messages being related to some macros which do not find them.

and Yes, it is a adp.
0
 

Author Comment

by:shmz
Comment Utility
by the way, I solved my initial question by adding:
ADODB.recordset
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
Comment Utility
Ahh... an ADP project...

They are a bit different because you are simply fronting a datasource and that is established for the project with connection properties. You only point to a database at a time, so, when you changed your connection to another database, then thats what you end up seeing (as you have now experienced).

Here is a bit of background for ADP : http://office.microsoft.com/en-us/access-help/create-an-access-project-HA010167953.aspx also worth noting that I think ADP has been depricated...

Normally currentdb is DAO and adodb normally accesses via "currentproject"

Thats probably why nominating your recordset as being adodb is automatically referencing "currentproject"

There are some good examples here : http://support.microsoft.com/kb/281998  including accessing a different database.

Also note that the connection is established with a "SET" command otherwise it opens a new connection with ADO... Trap...

e.g.

rs.ActiveConnection = CurrentProject.Connection
rs.Source = "SELECT * FROM MyTable"
rs.Open

the above tells ADO to open a new connection where as you really want to use the current connection...

Set rs.ActiveConnection = CurrentProject.Connection
rs.Source = "SELECT * FROM MyTable"
rs.Open

Or, you can use the "one liner" open statement

rs.Open "SELECT * FROM MyTable", CurrentProject.Connection [, ...]

0
 

Author Closing Comment

by:shmz
Comment Utility
Many Thanks
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now