Sh M
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.
now that I am connected to sql server I changed the sql = statement above to:
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
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
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 & "'));"
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
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.
==============
If you are just looking for existence, I don't see the need to return any data from the task table.
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.
sql = "select task.employeeid, task.id from task where (task.employeeid = " & parent![parent_id] & ") and (task.id='" & me.cmb_task & "');"
==============
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
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.
That should let you assign the Results from the SP ccall to a variable that you simply display in a message bow.
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?
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?
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
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?
ASKER
There is no table in access. all tables are in sql.
is the table attached?
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?
Does this answer your question?
do you see anything in the list of tables?
You might need to create LINKED TABLES to your SQL Server : http://office.microsoft.com/en-us/access-help/import-or-link-to-sql-server-data-HA010200494.aspx#BM2
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.
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.
>>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.
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?
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).
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).
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.
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.
ASKER
by the way, I solved my initial question by adding:
ADODB.recordset
ADODB.recordset
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many Thanks
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...
Open in new window