URGENT Help - upsizing access issues

[Bill]

Hi,

i just upgraded from access xp to adp with SQL backend. A couple of issues
i need help on URGENTLY:

1. i have managed to convert all my previous database
connection/interactivity to use adodb. eg:
On database startup i have:
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

cnn.Open ConnString

and when accessing underlying forms from vba code:
dim rst as new adodb.recordset
rst.activeconnection = <connection string>

where connection string is:
"Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=True;Data
Source=SQL;uid=***;pwd=***;Initial Catalog=ERGON Test;Data
Provider=SQLOLEDB.1"

the performance is now very slow. i think i am doing it wrong. Could
somebody PLEASE tell me what string i should use (if any), or the correct
way to connect to my sql database. I am a bit confused about the difference
between ADO, OLEDB, DAO etc... which doesn't help the issue. I would
appreciate if you simply treated my like a complete idiot and explain so
that i would understand.


2. the autonumber field in access works great. eg: when i open a form to
add a new record (whose underlying datasource is a table), as soon as i
start entering details into the new record, the autonumber is generated at
that instance. When using the ADP, the autonumber defaults to '1' until I
physically close the form. How can i get this number to be updated
immediately, as i refer to this particular identity field on the form to
perform lookups and other associated data entry?????

I am very confused...

Thanks for your help.
Bill

 

[Glen]

where connection string is:

"Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=True;Data
Source=SQL;uid=***;pwd=***;Initial Catalog=ERGON Test;Data
Provider=SQLOLEDB.1"

Your connection string should look more like:
Provider=sqloledb;Data Source=SQL;Initial
Catalog=ERGON;User Id=***;Password=***;"

I am a bit confused about the difference
between ADO, OLEDB, DAO etc... which doesn't help the issue. I would
appreciate if you simply treated my like a complete idiot and explain so
that i would understand.

In adp's ADO replaces DAO. DAO is a Jet thing. Once you
move over to SQL Server, you are forces to use ADO. Not a
bad thing though. The syntax is a little different but the
concept is the same. OLEDB is the provider to your adp so
your project can connected to a SQL Server database. See
File > Connection on the menu bar of your project for you
GUI interface.

2. the autonumber field in access works great. eg: when i open a form to
add a new record (whose underlying datasource is a table), as soon as i
start entering details into the new record, the autonumber is generated at
that instance. When using the ADP, the autonumber defaults to '1' until I
physically close the form. How can i get this number to be updated
immediately, as i refer to this particular identity field on the form to
perform lookups and other associated data entry?????

Welcome to the world of ADP-SQL Server. You will find
there are many things that a different from working with a
mdb file. Examples are as you described above. You will
find new properties of forms that need to be set for them
to properly work. Underlying recordsets of forms do not
refresh automatically to show changes made by other users.
The list goes on...

I swore up and down for the first month or so when I first
started working with adp's. Coming from mdb development, I
was expecting certain behaviour from my good old friend,
Access. I felt a little betrayed. If you'll wade through
these differences, which you will feel are bugs but really
aren't, and start learning and using the full power of SQL
Server, things will pay off for you. Big time! I can't
imaging creating the large multi-user databases that I've
completed with Access-Jet. I was forever bumping my head
on Access's limitation. Too complex of queries, network
traffic, etc. Once you learn what SQL and adp's can do for
you, you'll never create an app use a mdb again. Unless it
is an app to organize your grandmother recipes.

Ohhh, I can feel the flames already coming from the Access-
Jet arena with that last remark. Ah, but it's all good.

G-wiz, I've written a book. Sorry about that. Hey
Microsoft! Where's my salary for promoting your products.
Gz I'm work for free! The h*ll with that!

Hope I've helped you and inspired you in your dark hour.

Wish you well.

 

[Bill]

Hi Glen,

thanks for that - you have been most helpful.

Is there any more info you could shed on point 2.
I have now written a Stored Procedure and am returning the identity - the
problem is now that i don't know how to call it and retrieve the identity
field that it should return. I am open to your suggestions. My sp is below
and code i amcurrently using is as follows:


--IF NOT EXISTS (SELECT *
-- FROM dbo.sysobjects
-- WHERE id = OBJECT_ID('ProductEnquiry')
-- AND (OBJECTPROPERTY(id, 'IsProcedure') = 1))

--CREATE PROC statement must be the first statement in batch
--so need dynamic SQL


--CREATE PROCEDURE dbo.NewEnquiry

EXEC('CREATE PROC dbo.NewEnquiry AS SELECT 1 AS Col1')
GO
ALTER PROCEDURE [dbo].[NewEnquiry] (
@CustomerID VARCHAR(4),
@DateCreated DATE,
@DateLastModified DATE,
@EnquiryTypeID VARCHAR(4),
@CampaignID VARCHAR(4),
@ReferralTypeID VARCHAR(4),
@TransactionTypeID CARCHAR(4),
@StatusID VARCHAR(4),
@CancellationReason varchar(100),
@EnquiryNotes VARCHAR(100),
@InstallationNotes VARCHAR(100),
@DeliveryAddressLine1 VARCHAR(50),
@DeliveryAddressLine2 VARCHAR(50),
@DeliveryAddressLine3 VARCHAR(50),
@DeliveryAddressLine4 VARCHAR(50),
@DeliveryLocalityState VARCHAR(3),
@DeliveryLocalityPostCode VARCHAR(4),
@DeliveryTypeID VARCHAR(4),
@Postage MONEY,
@BrochureEnquiryNotes VARCHAR(100),
@username VARCHAR(20)
) AS

-- Modification History:
-- User Id Date Task# Description
-----------------------------------------------------------------------

-- Set NOCOUNT to on so that User Defined errors are returned
SET NOCOUNT ON

INSERT INTO [dbo].[ProductEnquiry](
[CustomerID], [DateCreated], [DateLastModified], [EnquiryTypeID],
[CampaignID],[ReferralTypeID],[TransactionTypeID],[StatusID],[CancellationRe
ason],
[EnquiryNotes],[InstallationNotes],
[DeliveryAddressLine1],[DeliveryAddressLine2],[DeliveryAddressLine3],[Delive
ryAddressLine4], [DeliveryLocalityState],[DeliveryLocalityPostcode],
[DeliveryTypeID], [Postage], [BrochureEnquiryNotes], [username]
)

VALUES(
@CustomerID,
GetDate(),
GetDate(),
@EnquiryTypeID,
@CampaignID,
@ReferralTypeID,
@TransactionTypeID,
@StatusID,
@CancellationReason,
@EnquiryNotes,
@InstallationNotes ,
@DeliveryAddressLine1,
@DeliveryAddressLine2,
@DeliveryAddressLine3,
@DeliveryAddressLine4,
@DeliveryLocalityState ,
@DeliveryLocalityPostCode,
@DeliveryTypeID,
@Postage,
@BrochureEnquiryNotes,
@username

-- '','','','','','','','','','','0','',''
)

SET NOCOUNT OFF
RETURN @@IDENTITY
,

Current database code (which doesn't work!)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = ConnString
cnn.Open

Set rst = cnn.Execute("NewEnquiry") <-------this gives me an error...
'expecting CustomerID'
if not rst.eof then
i = rst!customerID
end if

i presume i is able to get the customerid field from rst ... but can't get
past the line.

 

[SamSpudz]

Bill,


Use scope_identity() instead of @@identity to return the last value entered
into an identity column within the same scope.

e.g select scope_identity()

Sam



To return

Hi Glen,

thanks for that - you have been most helpful.

Is there any more info you could shed on point 2.
I have now written a Stored Procedure and am returning the identity - the
problem is now that i don't know how to call it and retrieve the identity
field that it should return. I am open to your suggestions. My sp is below
and code i amcurrently using is as follows:


--IF NOT EXISTS (SELECT *
-- FROM dbo.sysobjects
-- WHERE id = OBJECT_ID('ProductEnquiry')
-- AND (OBJECTPROPERTY(id, 'IsProcedure') = 1))

--CREATE PROC statement must be the first statement in batch
--so need dynamic SQL


--CREATE PROCEDURE dbo.NewEnquiry

EXEC('CREATE PROC dbo.NewEnquiry AS SELECT 1 AS Col1')
GO
ALTER PROCEDURE [dbo].[NewEnquiry] (
@CustomerID VARCHAR(4),
@DateCreated DATE,
@DateLastModified DATE,
@EnquiryTypeID VARCHAR(4),
@CampaignID VARCHAR(4),
@ReferralTypeID VARCHAR(4),
@TransactionTypeID CARCHAR(4),
@StatusID VARCHAR(4),
@CancellationReason varchar(100),
@EnquiryNotes VARCHAR(100),
@InstallationNotes VARCHAR(100),
@DeliveryAddressLine1 VARCHAR(50),
@DeliveryAddressLine2 VARCHAR(50),
@DeliveryAddressLine3 VARCHAR(50),
@DeliveryAddressLine4 VARCHAR(50),
@DeliveryLocalityState VARCHAR(3),
@DeliveryLocalityPostCode VARCHAR(4),
@DeliveryTypeID VARCHAR(4),
@Postage MONEY,
@BrochureEnquiryNotes VARCHAR(100),
@username VARCHAR(20)
) AS

-- Modification History:
-- User Id Date Task# Description
-----------------------------------------------------------------------

-- Set NOCOUNT to on so that User Defined errors are returned
SET NOCOUNT ON

INSERT INTO [dbo].[ProductEnquiry](
[CustomerID], [DateCreated], [DateLastModified], [EnquiryTypeID],
[CampaignID],[ReferralTypeID],[TransactionTypeID],[StatusID],[CancellationRe

ason],
[EnquiryNotes],[InstallationNotes],

[DeliveryAddressLine1],[DeliveryAddressLine2],[DeliveryAddressLine3],[Delive
ryAddressLine4], [DeliveryLocalityState],[DeliveryLocalityPostcode],
[DeliveryTypeID], [Postage], [BrochureEnquiryNotes], [username]
)

VALUES(
@CustomerID,
GetDate(),
GetDate(),
@EnquiryTypeID,
@CampaignID,
@ReferralTypeID,
@TransactionTypeID,
@StatusID,
@CancellationReason,
@EnquiryNotes,
@InstallationNotes ,
@DeliveryAddressLine1,
@DeliveryAddressLine2,
@DeliveryAddressLine3,
@DeliveryAddressLine4,
@DeliveryLocalityState ,
@DeliveryLocalityPostCode,
@DeliveryTypeID,
@Postage,
@BrochureEnquiryNotes,
@username

-- '','','','','','','','','','','0','',''
)

SET NOCOUNT OFF
RETURN @@IDENTITY
,

Current database code (which doesn't work!)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = ConnString
cnn.Open

Set rst = cnn.Execute("NewEnquiry") <-------this gives me an error...
'expecting CustomerID'
if not rst.eof then
i = rst!customerID
end if

i presume i is able to get the customerid field from rst ... but can't get
past the line.

Your connection string should look more like:
Provider=sqloledb;Data Source=SQL;Initial
Catalog=ERGON;User Id=***;Password=***;"


In adp's ADO replaces DAO. DAO is a Jet thing. Once you
move over to SQL Server, you are forces to use ADO. Not a
bad thing though. The syntax is a little different but the
concept is the same. OLEDB is the provider to your adp so
your project can connected to a SQL Server database. See
File > Connection on the menu bar of your project for you
GUI interface.


Welcome to the world of ADP-SQL Server. You will find
there are many things that a different from working with a
mdb file. Examples are as you described above. You will
find new properties of forms that need to be set for them
to properly work. Underlying recordsets of forms do not
refresh automatically to show changes made by other users.
The list goes on...

I swore up and down for the first month or so when I first
started working with adp's. Coming from mdb development, I
was expecting certain behaviour from my good old friend,
Access. I felt a little betrayed. If you'll wade through
these differences, which you will feel are bugs but really
aren't, and start learning and using the full power of SQL
Server, things will pay off for you. Big time! I can't
imaging creating the large multi-user databases that I've
completed with Access-Jet. I was forever bumping my head
on Access's limitation. Too complex of queries, network
traffic, etc. Once you learn what SQL and adp's can do for
you, you'll never create an app use a mdb again. Unless it
is an app to organize your grandmother recipes.

Ohhh, I can feel the flames already coming from the Access-
Jet arena with that last remark. Ah, but it's all good.

G-wiz, I've written a book. Sorry about that. Hey
Microsoft! Where's my salary for promoting your products.
Gz I'm work for free! The h*ll with that!

Hope I've helped you and inspired you in your dark hour.

Wish you well.

 

[Vadim Rapp]

B> ALTER PROCEDURE [dbo].[NewEnquiry] (
B> @CustomerID VARCHAR(4),
B> @DateCreated DATE,
.......
B> @BrochureEnquiryNotes VARCHAR(100),
B> @username VARCHAR(20)
B> ) AS

B> INSERT INTO [dbo].[ProductEnquiry](
B> [CustomerID], [DateCreated], [DateLastModified],
B> [DeliveryTypeID], [Postage],
B> [BrochureEnquiryNotes], [username] )

B> VALUES(
B> @CustomerID,
B> @Postage,
B> @BrochureEnquiryNotes,
B> @username

B> -- '','','','','','','','','','','0','',''
B> )


I wonder what's the point of having stored procedure without any logic,
consisting of a single sql statement. Having one more object to care about?



RETURN @@IDENTITY

....
Set rst = cnn.Execute("NewEnquiry") <-------this gives me an error...


1. in order to use output parameter in s.p, you have to use ADO command.
2. The less ADO you use in Access, the better. Try to have Access do the
things for you, instead of doing its work with ADO recordsets etc. Don't
open any new ado connections and don't create any recordsets, unless
absolutely necessary.


Vadim

 

[Ezekiël]

Glen,

I'm having the same problem as bill, but only for the first part of his
question. I'm a beginner with adp, so could you provide me a little code to
start with connecting to sqlserver.

I want to know where i should put the adodb connection method. Should i use
it in each form or in a startup module like bill has.

I would be very happy to achieve this little step.

Greetings,

Ezekiël