June 11, 2003 at 6:47 pm
I have an SQL table:
PK pls_number int not null,
PK ur_number char(12) not null,
referred_from varchar(24) null,
test_requirements varchar(24) null,
referral_by int not null,
PK referral_date smalldatetime not null
with the three fields indicated forming the primary key.
I have VERY limited experience in Access which is being used as front
end for users (designed by someone else). Initially I have designed the
'referral_date' field to have a default value of getdate() in SQL.
However Access stumbles on this if it tries to return an empty field &,
I assume, overwrites the default but realises it can't because this is
part of the primary key.
OK so I designed an SQL query within Access consisting of simply:
select getdate()
it runs just fine as a query in isolation but I cannot find a way of
returning the results of this query to the Access 'referral_date'
field when folks are adding new data. It is simply meant to be a
unique timestamp & part of a foreign key for another table. I know
Access can be used to insert a date itself but I'd prefer to use the
timestamp returned by the sever.
Thanks for any help
Graeme
June 14, 2003 at 6:46 am
Maybe you could simplfy matters for yourself by creating a single column surrogate PK, and that would help Access with the date handling routines.
June 16, 2003 at 2:16 am
Hi Graeme,
quote:
I have VERY limited experience in Access which is being used as frontend for users (designed by someone else). Initially I have designed the
'referral_date' field to have a default value of getdate() in SQL.
However Access stumbles on this if it tries to return an empty field &,
I assume, overwrites the default but realises it can't because this is
part of the primary key.
is referral_date editable by the user? When you have the default value of getdate() how come Access returns an empty field?
quote:
OK so I designed an SQL query within Access consisting of simply:select getdate()
it runs just fine as a query in isolation but I cannot find a way of
returning the results of this query to the Access 'referral_date'
field when folks are adding new data. It is simply meant to be a
unique timestamp & part of a foreign key for another table. I know
Access can be used to insert a date itself but I'd prefer to use the
timestamp returned by the sever.
I've checked this and it seems to work as long as you let SQL Server do the job on referral_date, eg. it is not editable by the user.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 16, 2003 at 11:18 pm
quote:
--------------------------------------------------------------------------------
is referral_date editable by the user? When you have the default value of getdate() how come Access returns an empty field?
--------------------------------------------------------------------------------
OK, Frank, referral_date is not editable by the user. But the Access guy tried inserting a date & time from access - not sure how he achieved this.
When a new row is created in Access it returns an error if the referral_date is left empty. I created a new access interface to the sql table & simply tried to add a row in the table view of Access without any functions running & still got an error message and Access closes down in error. However, when I open access again & see the same table the new row has been added with the datetime from SQL Server which is what I want but ideally without the crash everytime I add a row.
I should add there are foreign key links with other tables but I am careful to add only examples that are kosher on this front. Using enterprise manager I can easily add rows directly to the tables without error. It's just that for users we need the Access front end.
quote:
--------------------------------------------------------------------------------
I've checked this and it seems to work as long as you let SQL Server do the job on referral_date, eg. it is not editable by the user.
--------------------------------------------------------------------------------
When you say "not editable by the user", what exactly do you mean? In the table view of Access all columns are editable? In the original front end there was a form that hid the field from the user but actually used Access to generate the date/time which I want to prevent.
Graeme
Edited by - Graeme Riddle on 06/16/2003 11:24:33 PM
Edited by - Graeme Riddle on 06/16/2003 11:29:22 PM
June 17, 2003 at 1:47 am
Hi Graeme,
quote:
But the Access guy tried inserting a date & time from access - not sure how he achieved this.
one line of vba code will do this.
quote:
When a new row is created in Access it returns an error if the referral_date is left empty. I created a new access interface to the sql table & simply tried to add a row in the table view of Access without any functions running & still got an error message and Access closes down in error. However, when I open access again & see the same table the new row has been added with the datetime from SQL Server which is what I want but ideally without the crash everytime I add a row.
What I did, was create a table in SQL Server with a datetime field default getdate(), link this within Access, and play in Access table view. I haven't received an error, I saw the entered data right when I saved the record. I could even overwrite the data or leave it blank (interestingly Allow null is not checked).
Are there any special db options checked for your front end?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 18, 2003 at 4:41 pm
I just created a new table 'testing':
lname
fname
mydate
with PK defined as lname fname
default for mydate is getdate() in SQL
Table view entry from Access works just fine (nothing is entered into mydate) with the datetime returned via SQL to the mydate field as I move to the next row.
Next
I redefine Primary key to be lname mydate in SQL Enterprise Manager.
Table entry from Access works just fine (nothing is entered into mydate)& I fall off my chair. OK so one more thing; drop the Access connection to this table & re-link. Data entry results in #Deleted in each field in Access when I enter 'lname & fname & move to the next row. Now I close the table view in Access and re-open it and there's the correct values for lname, fname & the datetime returned by SQL diplayed.
How do I check if there are any special db options checked for the front end? The Access guy is on leave at the moment so I'll have to check this myself.
Graeme
Edited by - Graeme Riddle on 06/18/2003 4:45:33 PM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply