Access Data Projects Uniqueidentifier problem

Hi,

I am designing a SQL server backend, MS Access (ADP) frontend
application and I came across an interesting problem.
Lets say I have a table T with the following fields(columns)
[a] (int Not Null, Primary key, Identity auto increment)
[b] (uniqueidentifier, Allow Nulls)
[c] (text)

When I open the table in datasheet view using Access and type a value
in [c], [a] and [b] get populated automatically. [a] getting filled
with incremental integers is understandable. But, I have not set the
ISRowGUID or a default value for [b] and still it gets popullated with
a random GUID. If I use SQL Server enterprise manager to enter data
into the table T, [b] does not get filled and stays Null. I forgot to
mention that there are no relationships between the tables yet.
Is it MS access that is responsible or is it me? In any case can
something be dont to prevent this? Also should there be something else
in relation to this that I have to look for in the future.

My best
Ibrahim MMSSQL itself won't put in a value unless you have a column default or a
trigger on the table, so it seems likely that Access is doing this. You
could use Profiler to see exactly what commands Access sends to the
server when you use the datasheet, but if you want to know why it does
that, then I guess you'll get a better answer in an Access group.

Simon|||Thanks Simon,

The "problem" in fact does lie in MS Access.

I found a fix here
http://www.developersdex.com/sql/me...p=581&r=3822184

Thanks a lot for all the help !
My best
Ibrahim M

Access as front end, SQL Server as backend

What are the pro's and con's of an application that uses
Access as the user interface and SQL Server as the backend
database, compared with if VB is used to develop the front
end instead?DBA,
VB is more flexible but may require more expensive resources. Using VB
is more likely, but not necessarily, to produce a more stable, scalable
application. I would recommend using a .NET language such as VB.NET
It also depends on the skilled resources you have available to draw on.
If you only have Access developers, then you are limited to those,
unless you want to pay for a VB.NET developer.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DBA wrote:
> What are the pro's and con's of an application that uses
> Access as the user interface and SQL Server as the backend
> database, compared with if VB is used to develop the front
> end instead?|||In addition to what Mark said, you have to consider what your app is
being used for, how many concurrent users will be updating data, what
is the deployment model (intranet, internet, local), is it an OLAP or
OLTP app or a combo, do you need reports, how much budget, existing
expertise, etc. The suitability of any particular client application
is highly dependent on the answers to these questions. I'd recommend a
needs analysis where you formally go through the process of asking and
researching the answers to these questions.
--Mary
On Thu, 22 Jul 2004 21:17:13 -0700, "DBA"
<anonymous@.discussions.microsoft.com> wrote:
>What are the pro's and con's of an application that uses
>Access as the user interface and SQL Server as the backend
>database, compared with if VB is used to develop the front
>end instead?

Access as front end, SQL Server as backend

What are the pro's and con's of an application that uses
Access as the user interface and SQL Server as the backend
database, compared with if VB is used to develop the front
end instead?DBA,
VB is more flexible but may require more expensive resources. Using VB
is more likely, but not necessarily, to produce a more stable, scalable
application. I would recommend using a .NET language such as VB.NET
It also depends on the skilled resources you have available to draw on.
If you only have Access developers, then you are limited to those,
unless you want to pay for a VB.NET developer.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DBA wrote:
> What are the pro's and con's of an application that uses
> Access as the user interface and SQL Server as the backend
> database, compared with if VB is used to develop the front
> end instead?|||In addition to what Mark said, you have to consider what your app is
being used for, how many concurrent users will be updating data, what
is the deployment model (intranet, internet, local), is it an OLAP or
OLTP app or a combo, do you need reports, how much budget, existing
expertise, etc. The suitability of any particular client application
is highly dependent on the answers to these questions. I'd recommend a
needs analysis where you formally go through the process of asking and
researching the answers to these questions.
--Mary
On Thu, 22 Jul 2004 21:17:13 -0700, "DBA"
<anonymous@.discussions.microsoft.com> wrote:

>What are the pro's and con's of an application that uses
>Access as the user interface and SQL Server as the backend
>database, compared with if VB is used to develop the front
>end instead?

Access as front end, SQL Server as backend

What are the pro's and con's of an application that uses
Access as the user interface and SQL Server as the backend
database, compared with if VB is used to develop the front
end instead?
DBA,
VB is more flexible but may require more expensive resources. Using VB
is more likely, but not necessarily, to produce a more stable, scalable
application. I would recommend using a .NET language such as VB.NET
It also depends on the skilled resources you have available to draw on.
If you only have Access developers, then you are limited to those,
unless you want to pay for a VB.NET developer.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
DBA wrote:
> What are the pro's and con's of an application that uses
> Access as the user interface and SQL Server as the backend
> database, compared with if VB is used to develop the front
> end instead?
|||In addition to what Mark said, you have to consider what your app is
being used for, how many concurrent users will be updating data, what
is the deployment model (intranet, internet, local), is it an OLAP or
OLTP app or a combo, do you need reports, how much budget, existing
expertise, etc. The suitability of any particular client application
is highly dependent on the answers to these questions. I'd recommend a
needs analysis where you formally go through the process of asking and
researching the answers to these questions.
--Mary
On Thu, 22 Jul 2004 21:17:13 -0700, "DBA"
<anonymous@.discussions.microsoft.com> wrote:

>What are the pro's and con's of an application that uses
>Access as the user interface and SQL Server as the backend
>database, compared with if VB is used to develop the front
>end instead?

Access 2000 Frontend MS SQL 2000 backend - Locking Problems

We are using an Access 2000 project to view our SQL Tables and using Access 2000 Runtime to Access the forms in the project. We have written in a locking system in VB and removed the video controls to prevent users from accessing the same records. But of course now we need to make the video controls available. This has now thrown up the problem of multiple users accessing the same records. We have tried to write code to lock records when then video controls are used but this is not working as well as we hoped. Can anyone please suggest any way of setting up locking on SQL using triggers from the Access frontend? or any other types of locking systems that could be written in the Access front end.

The safest and easiest way to use locks having to do with SQL Server in a distributed application is to use SQL Server locks themselves. See the links below for information on how SQL Server locks work and how to use them.

http://msdn2.microsoft.com/en-us/library/ms189857.aspx

http://www.informit.com/articles/article.aspx?p=26657&rl=1

http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

http://www.databasejournal.com/features/mssql/article.php/3289661

http://www.sql-server-performance.com/articles/per/advanced_sql_locking_p1.aspx

http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/

Hope that helps,

John

Access 2000 Frontend MS SQL 2000 backend - Locking Problems

We are using an Access 2000 project to view our SQL Tables and using Access 2000 Runtime to Access the forms in the project. We have written in a locking system in VB and removed the video controls to prevent users from accessing the same records. But of course now we need to make the video controls available. This has now thrown up the problem of multiple users accessing the same records. We have tried to write code to lock records when then video controls are used but this is not working as well as we hoped. Can anyone please suggest any way of setting up locking on SQL using triggers from the Access frontend? or any other types of locking systems that could be written in the Access front end.

The safest and easiest way to use locks having to do with SQL Server in a distributed application is to use SQL Server locks themselves. See the links below for information on how SQL Server locks work and how to use them.

http://msdn2.microsoft.com/en-us/library/ms189857.aspx

http://www.informit.com/articles/article.aspx?p=26657&rl=1

http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

http://www.databasejournal.com/features/mssql/article.php/3289661

http://www.sql-server-performance.com/articles/per/advanced_sql_locking_p1.aspx

http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/

Hope that helps,

John

Access 2000 forms record count wont show

I have a SQL Server 2000 backend with an Access 2000 .mdb frontend using ODBC to connect to the SQL tables.

I have forms with their Recordsource set to the SQL tables but when I open the forms, the total record count isn't shown beside the record navigator unless I click to go to the end of the recordset. I really need the record count to show up.

HELP!This question really belongs in the Acces forum, but try creating an On Open event in your form with this code:

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst
End Sub

blindman