Showing posts with label couple. Show all posts
Showing posts with label couple. Show all posts

Monday, March 26, 2012

Newbie Question - Edit data on SQL Server using Data access pages

I have been and SQL administrator for about 6 hours now. I grabbed a couple
books and have been doing searches online and reading through some MSDN
online stuff. Having never seen SQL but having done some Access it wasn't
too hard to get a database set up on the server.
I managed to import an old access database with tables into the SQL database
on the server. Easy enough. I then managed to set up a new Access Project
on a local machine pointing at the server and connect to the database quite
easily.
Then I built an form based on the primary table. Easy enough. I had to go
into form properties and enable edit so that the form would open. Otherwise
it just opened as a blank form page with no fields.
I can also build data access pages that display all the data in the database
which is wonderful and I'm really starting to see how powerful and wonderful
SQL is going to be.
Only problem is everything I do creates read only. I can do a data access
page, a form or a table but even with edit enabled I only get read only
access to the data. Any attempts to modify data results in the following
message in the taskbar.
On data access page: No message just don't allow edits or typing.
On Form in access: Field "XXX" is based on an expression and cant be
edited.
On Table view: The recordset is not updatable.
I'm sure once I spend a few more hours beating around I'll figure this out
and I'm sure its just because of my naivety in all things SQL but I was
hoping someone would have a quick pointer to get me back on the development
track.
Thanks again for all your help.
DocThe SQL server user account accessing the SQL server from access has to
have write access.
and more importantly the table needs to have a unique index else you will
not be able to edit the recordset/Table in access.|||Check if the table has a primary key, if not, create the pk and them relink
the tables.
AMB
"Donald Watson" wrote:
> I have been and SQL administrator for about 6 hours now. I grabbed a couple
> books and have been doing searches online and reading through some MSDN
> online stuff. Having never seen SQL but having done some Access it wasn't
> too hard to get a database set up on the server.
> I managed to import an old access database with tables into the SQL database
> on the server. Easy enough. I then managed to set up a new Access Project
> on a local machine pointing at the server and connect to the database quite
> easily.
> Then I built an form based on the primary table. Easy enough. I had to go
> into form properties and enable edit so that the form would open. Otherwise
> it just opened as a blank form page with no fields.
> I can also build data access pages that display all the data in the database
> which is wonderful and I'm really starting to see how powerful and wonderful
> SQL is going to be.
> Only problem is everything I do creates read only. I can do a data access
> page, a form or a table but even with edit enabled I only get read only
> access to the data. Any attempts to modify data results in the following
> message in the taskbar.
> On data access page: No message just don't allow edits or typing.
> On Form in access: Field "XXX" is based on an expression and cant be
> edited.
> On Table view: The recordset is not updatable.
> I'm sure once I spend a few more hours beating around I'll figure this out
> and I'm sure its just because of my naivety in all things SQL but I was
> hoping someone would have a quick pointer to get me back on the development
> track.
> Thanks again for all your help.
> Doc
>
>|||It was the missing primary key.
Now I can develop to my hearts content.
Thanks so much for the fast response.
Doc

Newbie question

I have installed MSDE2000 since a couple of weeks on my machine and its
running the database for a forum on the intranet. Could someone please tell
me, how can I make a regular back up of my database please?
Regards
Rahul
hi Rahul,
"Rahul Kumar" <rahul.kumar.remove_it@.sage.remove_it.com> ha scritto nel
messaggio news:eQTRboBlEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have installed MSDE2000 since a couple of weeks on my machine and its
> running the database for a forum on the intranet. Could someone please
tell
> me, how can I make a regular back up of my database please?
as MSDE does not provide graphical management utilities, you should resort
on Transact-SQL statement execution via oSql.exe, the command line
management tool available and bundled with it...
further info on how to use it can be found at
http://support.microsoft.com/default...;EN-US;q325003
for your convenience, you can have a look at a free prj of mine, available
at the link following my sign., which porvides a user interface similar to
Enterprise Manager, that includes that feature, graphically
further MSDE management tools, both free and commercial, can be found at
http://www.microsoft.com/sql/msde/partners/default.asp and/or
http://www.aspfaq.com/show.asp?id=2442
google for others
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Adrea! That was a great help!
Regards
Rahul
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2q3b2oFq9t8lU1@.uni-berlin.de...
> hi Rahul,
> "Rahul Kumar" <rahul.kumar.remove_it@.sage.remove_it.com> ha scritto nel
> messaggio news:eQTRboBlEHA.3536@.TK2MSFTNGP12.phx.gbl...
> tell
> as MSDE does not provide graphical management utilities, you should resort
> on Transact-SQL statement execution via oSql.exe, the command line
> management tool available and bundled with it...
> further info on how to use it can be found at
> http://support.microsoft.com/default...;EN-US;q325003
> for your convenience, you can have a look at a free prj of mine, available
> at the link following my sign., which porvides a user interface similar to
> Enterprise Manager, that includes that feature, graphically
> further MSDE management tools, both free and commercial, can be found at
> http://www.microsoft.com/sql/msde/partners/default.asp and/or
> http://www.aspfaq.com/show.asp?id=2442
> google for others
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

Saturday, February 25, 2012

Newbie - Program

This is my first foray into MS SQL Server, so I am trying to get my feet wet
with a couple of things. One thing I am trying to do right now is fill in a
uniqueidentifier field I added to a table using the NEWID() function. In MS
Access, I would have cranked out a little VBA code that simply went through
all the records in the table one by one and setting the Record_ID (type
uniqueidentifier) field using NEWID().
Unfortunately, I don't have a warm and fuzzy on how to accomplish this
within SQL Server itself. Best I have been able to tell so far, I probably
want to be developing a procedure. But I am not sure how to accomplish what
I want to do using SQL.
Suggestions and pointers to references will be greatly appreciated!
Thanks!
DonHello, Don
You only need:
UPDATE YourTable SET YourColumn=NEWID()
That's because the NEWID() function will be called for each row
(therefore you will get a different value in each row).
Razvan|||In SQL server there is a thing called default value
You will see it in Enterprise Manager when you click on design table
In the default value property of a field you can type newid() and whenever a
new record is inserted it will 'automatically' get this value
Default values are very handy for Created Date you would give it a default
value of getdate()
Hope this helped you and did not add to the confusion
http://sqlservercode.blogspot.com/
"Don" wrote:

> This is my first foray into MS SQL Server, so I am trying to get my feet w
et
> with a couple of things. One thing I am trying to do right now is fill in
a
> uniqueidentifier field I added to a table using the NEWID() function. In
MS
> Access, I would have cranked out a little VBA code that simply went throug
h
> all the records in the table one by one and setting the Record_ID (type
> uniqueidentifier) field using NEWID().
> Unfortunately, I don't have a warm and fuzzy on how to accomplish this
> within SQL Server itself. Best I have been able to tell so far, I probabl
y
> want to be developing a procedure. But I am not sure how to accomplish wh
at
> I want to do using SQL.
> Suggestions and pointers to references will be greatly appreciated!
> Thanks!
> Don
>
>
>|||SQL,
While I was playing around, I discovered the default value and that if you
select that property when you create the field it will automatically fill in
values! In my first attempt, I had created the field, but not set the
default value so I had a table full of <Null> for that field (never mind the
issue of adding a new record later!).
Thanks for the help!!
Don
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:75975E43-A123-43DC-96C3-B120D0B35BF0@.microsoft.com...
> In SQL server there is a thing called default value
> You will see it in Enterprise Manager when you click on design table
> In the default value property of a field you can type newid() and whenever
> a
> new record is inserted it will 'automatically' get this value
> Default values are very handy for Created Date you would give it a default
> value of getdate()
> Hope this helped you and did not add to the confusion
> http://sqlservercode.blogspot.com/
>
> "Don" wrote:
>|||Razvan,
I actually solved my immediate problem a little differently, but your
suggestion will definitely help in the future!!
Thanks!
Don
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1127663811.868041.231450@.g43g2000cwa.googlegroups.com...
> Hello, Don
> You only need:
> UPDATE YourTable SET YourColumn=NEWID()
> That's because the NEWID() function will be called for each row
> (therefore you will get a different value in each row).
> Razvan
>|||Your entire mental model of SQL and RDBMS is totally wrong and will
take at least one year of solid work to change. This is based on 20+
years with SQL and teaching it.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. NEWID and uniqueidentifier "field" are proprietary,
non-relational crap. Since you started with ACCESS, you need to add an
extra six months of UN-learning.
A newsgroup is a piss poor substitue for an education. Please read
something before you post again.