Runtime error 3704

Hi,

I've created an application that runs on several pc's. However, on one particular pc, when I run the following code to test the connection to the SQL server, it generates the error "Runtime error '3704'" and it happens only when it hits the Form1.Users.Movefirst section.

I've checked all the user permissions on the server and dont seem to find any difference there.

code:-

Option Explicit
Public Db As New ADODB.Connection
Public Users As New ADODB.Recordset

Private Sub Command1_Click()

If Not Form1.Users.BOF Then
Form1.Users.MoveFirst
End If

With Form1.Users
Do While Not Form1.Users.EOF
Debug.Print Form1.Users.Fields(0)
Form1.Users.MoveNext
Loop
End With

End Sub

Private Sub Form_Load()

On Error Resume Next
Set Db = Nothing

Db.Open _
"Provider=SQLOLEDB.1;Server=File;" & _
"Database=Cash Management;Trusted_Connection=Yes"

'Open the recordsets
With Form1.Users
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.ActiveConnection = Form1.Db
.Source = "Select * from Users order by userid"
.Open "SET NOCOUNT ON"
End With

End Sub

ThanksIt is very simple. You are running "SET NOCOUNT ON" only. What MDAC version do you use?|||Hi.. Thanks for your reply. Normally, my .open has nothing after it. Only after I searched on the net, did I add that "Set NoCount On", but it didn't help. Sorry to sound ignorant, but how do I tell what ver of MDAC I'm using ?

Carl|||Q1. What MDAC version am I using?
A1. Use regedit.exe, see branch "HKEY_LOCAL_MACHINE\Software\Microsoft\DataAccess" node "FullInstallVer"|||It looks like 2.70.7713.4.

Tks..|||You added "SET NOCOUNT ON" for test purposses only, remove it.
In your code, after the part where you try to open a connection,
add a check (+msgbox) to know if it is really connected.
It can be a domain security problem.|||Ok - I checked the registry on the pc that I was installing on and I see the version of MDAC is 2.53.6200.1. This is the same as another pc that is working.

If this is not the problem, I suspect that it is a permissions problem, but I have no idea where to check as this user has been registered exactly the same as the other users that work.

Thanks|||Run (for W9x)
command /K net config
from Start->Run commandline on the client machine and you will see his logon info (or ask him to do it and send results).|||My email address is alkemac@.hotmail.com. Can you please send an email and I will reply with screen shots of the different screens, including the SQL Svr user registration screen ?

Thanks|||It was a user default database problem. The user solved it by himself.|||What do you mean by "default database problem"? Can you explain how the user solved it himself? We have a similar problem with a VB application that opens an Access database. It runs on most computers, but not some. One gets the 3704 runtime error. Thanks.sql

Running total with "treshold value" detection..?

Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.
Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
selectdatepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>
|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:

Running total with "treshold value" detection..?

Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.
Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
selectdatepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>
|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:

Running total with "treshold value" detection..?

Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
select datepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jéjé wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jéjé wrote:
>> Hi,
>> I have to create a query which evalaute, for each year, the employees who
>> reached a particular number of absences and when this value is reached.
>> I have 1 000 000 records in my absence table.
>> my table is like this:
>> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
>> I want a list of 1 date by year by employee and by activity when the
>> runin absenceduration total reached 5 days.
>> Also, I'll evaluate the same formula but by quarter instead-of year and
>> the target value is 3 days.
>> AbsenceDurationInDays is a floating value like:
>> 0.5 = half a day (or 4hours of work)
>> any guide?
>> any sample query anywhere?
>> how to setup my indexes?
>> I'll store the result in a table using DTS, and I'll evaluate this query
>> 1 time a week.
>> thanks for your help.
>> Jerome.

Running total with "treshold value" detection..?

Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
select datepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and th
e
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:sql

Running SQL Server Stored Procedures through access

Hi,
Can someone help me with this problem.
I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:

Dim cn, cmd
Set cn = CreateObject("ADODB.Connection")
cn.Open "SQL" //Data Source Name
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "LoadApplicants" //Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Execute

for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?lets see the code for the sp|||There is definitely a reason, although I can't see what it is yet.

My first guess would be an object ownership problem... I'd prefix any object names that don't contain a period (.) with "dbo." to make them valid "two part names" for SQL Server. This may not be your problem, but it is the best place I can think of to start.

-PatP|||I would explicitly dim your cn and cmd objects.

Dim cn as new ADODB.connection
Dim cmd as new ADODB.command

At the bottom of your function, make sure you destroy the objects

cn.close
Set cn = Nothing
Set cmd = Nothing

Try using a OLEDB connection instead of a DSN.

Are there any parameters for this stored procedure? I do not see them here.

Maybe also try fully qualifying the stored procedure name because you might be hitting the wrong proc.

[databasename].[owner (usually dbo)].procedurename.|||a quick check that has worked for me has been to create an access db PROJECT (*.adp)
set the datasource to your sql server and the db that you want to connect to and try to access your stored procedures
if it works you can assume (to a degree) that you have parity.