Solved

Check duplicate entries in access 2007 vba, sql 2008

Posted on 2011-09-17
21
406 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
ID: 36555909
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
ID: 36555914
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
ID: 36556230
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 22

Expert Comment

by:8080_Diver
ID: 36556461
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
ID: 36556546
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
ID: 36556576
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
ID: 36556646
Is the SQL Server version of the tasks table an attached table to the Access database?
0
 

Author Comment

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

Expert Comment

by:aikimark
ID: 36556685
is the table attached?
0
 

Author Comment

by:shmz
ID: 36556704
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
 
LVL 45

Expert Comment

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

Expert Comment

by:Mark Wills
ID: 36556763
0
 

Author Comment

by:shmz
ID: 36556889
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
ID: 36556914
@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
ID: 36557021
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
ID: 36557895
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
ID: 36584807
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
ID: 36584832
by the way, I solved my initial question by adding:
ADODB.recordset
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 36584976
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
ID: 36595388
Many Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 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