Link to home
Start Free TrialLog in
Avatar of Sh M
Sh MFlag for United States of America

asked on

Check duplicate entries in access 2007 vba, sql 2008

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
Avatar of Mark Wills
Mark Wills
Flag of Australia image

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

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.
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.
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.
Avatar of Sh M

ASKER

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?
Avatar of Sh M

ASKER

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
Is the SQL Server version of the tasks table an attached table to the Access database?
Avatar of Sh M

ASKER

There is no table in access. all tables are in sql.
is the table attached?
Avatar of Sh M

ASKER

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?

do you see anything in the list of tables?
Avatar of Sh M

ASKER

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.
@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.
Avatar of Sh M

ASKER

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?
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).
Avatar of Sh M

ASKER

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.
Avatar of Sh M

ASKER

by the way, I solved my initial question by adding:
ADODB.recordset
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sh M

ASKER

Many Thanks