Upsizing MS Access - lessons learnt - Part 2 (Testing) + Insert Into SQL table

[ImraneA]

Hi there

I had pleasure of upsizing Access v97 db to Access v2K/SQL 2K.

Wish to provide some knowledge gained back to community - hopefully
help others.

1.Question how do you test stored procedure from SQL Server vs MS
Access point of view ?

2.How do you insert many records at same time ?

Questions may seem straight forward to some but I found very
frustrating.

Here goes :-

N.B.
1.Strings e.g. ' or "

See below my sproc.

1.************* *************** *************** **************

A.SQL Server
i.

Query Analyzer - debug procedure - value

'HARLEY.I','030 04','A000-AA00',NULL,NULL ,getdate(),2003-08-29,0,0,7.5,7.5, 7.5,7.5,7.0,'No tes','General', 1,2,3,getdate() ,1,getdate(),nu ll,NULL,0,null, 0,null;'HARLEY. I','03004','A00 0-AA00',NULL,NULL ,getdate(),2003-08-29,0,0,7.5,7.5, 7.5,7.5,7.0,'No tes','General', 1,2,3,getdate() ,1,getdate(),nu ll,null,0,null, 0,null;

ii.

Query Analyzer

DECLARE @RetCode int
DECLARE @RetMsg varchar(100)
EXEC procTimesheetIn sert
@TimesheetDetai ls
='''HARLEY.I'', ''03004'',''A00 0-AA00'',NULL,NUL L,getdate(),200 3-08-29,0,0,7.5,7.5, 7.5,7.5,7.0,''N otes'',''Genera l'',1,2,3,getda te(),1,getdate( ),null,NULL,0,n ull,0,null;''HA RLEY.I'',''0300 4'',''A000-AA00'',NULL,NUL L,getdate(),200 3-08-29,0,0,7.5,7.5, 7.5,7.5,7.0,''N otes'',''Genera l'',1,2,3,getda te(),1,getdate( ),null,null,0,n ull,0,null;',
@RetCode = @RetCode OUTPUT,
@RetMsg = @RetMsg OUTPUT,
@TimesheetID = NULL

B.Access Side

i.ADO conection

..Source = "SELECT '''' & [re_code] & '''' AS RE_Code1, Test.PR_Code,
'''' & [AC_Code] & '''' AS AC_Code1, Test.CO_Code, Test.CH_Code,
'getdate()' AS TS_LastEdit1, Test.WE_Date, Test.SAT, Test.SUN,
Test.MON, Test.TUE, Test.WED, Test.THU, Test.FRI, '''' & [NOTES] &
'''' AS NOTES1, '''' & [GENERAL] & '''' AS GENERAL1, Test.PO_Number,
Test.WWL_Number , Test.CN_Number, 'getdate()' AS Last_Editby1, '1' AS
User_Confirm1, 'getdate()' AS User_Confirm_Da te1, Test.Appd_By,
Test.Appd_Date, '0' AS Approved1, '' AS a, Test.Concurrenc yID, '' AS b
FROM Test;"

2.************* *************** *************** **************
To separate data use ";" - see data

'HARLEY.I','030 04','A000-AA00',NULL,NULL ,getdate(),2003-08-29,0,0,7.5,7.5, 7.5,7.5,7.0,'No tes','General', 1,2,3,getdate() ,1,getdate(),nu ll,NULL,0,null, 0,null;'HARLEY. I','03004','A00 0-AA00',NULL,NULL ,getdate(),2003-08-29,0,0,7.5,7.5, 7.5,7.5,7.0,'No tes','General', 1,2,3,getdate() ,1,getdate(),nu ll,null,0,null, 0,null;

*************** *************** *************** **************
CREATE PROCEDURE [dbo].[procTimesheetIn sert](
@TimesheetDetai ls varchar(5000) = NULL,
@RetCode int = NULL OUTPUT,
@RetMsg varchar(100) = NULL OUTPUT,
@TimesheetID int = NULL)
WITH RECOMPILE
AS
SET NOCOUNT ON

DECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)
DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)
DECLARE @TimesheetCount int, @TimesheetCount 1 int
DECLARE @TS_LastEdit smalldatetime
DECLARE @Last_Editby smalldatetime
DECLARE @User_Confirm bit
DECLARE @User_Confirm_D ate smalldatetime
DECLARE @DetailCount int
DECLARE @Error int
/* Validate input parameters. Assume success. */

SELECT @RetCode = 1, @RetMsg = ''

IF @TimesheetDetai ls IS NULL
SELECT @RetCode = 0,
@RetMsg = @RetMsg +
'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)

/* Create a temp table parse out each Timesheet detail from input
parameter string,
count number of detail records and create SQL statement to
insert detail records into the temp table. */
CREATE TABLE #tmpTimesheetDe tails
/*
(Counter int IDENTITY (1,1),

SELECT @SQLBase ='INSERT INTO
#tmpTimesheetDe tails(RE_Code,P R_Code,AC_Code, WE_Date,SAT,SUN ,MON,TUE,WED,TH U,FRI,Notes,Gen eral,PO_Number, WWL_Number,CN_N umber,User_Conf irm,User_Confir m_Date,Approved ,ConcurrencyID)
*/

(
RE_Code varchar(50),
PR_Code varchar(50),
AC_Code varchar(50),
CO_Code varchar(50),
CH_Code varchar(50),
TS_LastEdit smalldatetime,
WE_Date smalldatetime,
SAT REAL DEFAULT 0,
SUN REAL DEFAULT 0,
MON REAL DEFAULT 0,
TUE REAL DEFAULT 0,
WED REAL DEFAULT 0,
THU REAL DEFAULT 0,
FRI REAL DEFAULT 0,
Notes varchar(255),
General varchar(50),
PO_Number REAL,
WWL_Number REAL,
CN_Number REAL,
Last_Editby varchar(50),
User_Confirm bit,
User_Confirm_Da te smalldatetime,
Appd_By varchar(50),
Appd_Date smalldatetime,
Approved bit,
POSTED_TO_TES smalldatetime,
ConcurrencyID int,
[Timestamp] varchar(50))
SELECT @SQLBase ='INSERT INTO
#tmpTimesheetDe tails(RE_Code,P R_Code,AC_Code, CO_Code,
CH_Code,TS_Last Edit,WE_Date,SA T,SUN,MON,TUE,W ED,THU,FRI,Note s,General,PO_Nu mber,WWL_Number ,CN_Number,Last _Editby,
User_Confirm,Us er_Confirm_Date ,Appd_By
,Appd_Date,Appr oved,POSTED_TO_ TES,Concurrency ID,[Timestamp])
VALUES ( '
SELECT @TimesheetCount =0
WHILE LEN( @TimesheetDetai ls) > 1
BEGIN
SELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetai ls,
Charindex(';', @TimesheetDetai ls) -1) + ')'
EXEC(@SQLComple te)
SELECT @TimesheetCount = @TimesheetCount + 1
SELECT @TimesheetDetai ls = RIGHT( @TimesheetDetai ls, Len(
@TimesheetDetai ls)-Charindex(';', @TimesheetDetai ls))
END

IF (SELECT Count(*) FROM #tmpTimesheetDe tails) <> @TimesheetCount
SELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Details
couldn''t be saved.' + CHAR(13) + CHAR(10)

-- If validation failed, exit proc
IF @RetCode = 0
RETURN

-- If validation ok, continue
SELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +
CHAR(10)
/* RETURN*/

-- Start transaction by inserting into Timesheet table
BEGIN TRAN
INSERT INTO timesheet.dbo.W WL_Timesheets
select RE_Code,PR_Code ,AC_Code,CO_Cod e,
CH_Code,TS_Last Edit,WE_Date,SA T,SUN,MON,TUE,W ED,THU,FRI,Note s,General,PO_Nu mber,WWL_Number ,CN_Number,Last _Editby,
User_Confirm,Us er_Confirm_Date ,Appd_By
,Appd_Date,Appr oved,POSTED_TO_ TES,Concurrency ID,[Timestamp]
FROM #tmpTimesheetDe tails
/*
SELECT RE_Code, PR_Code,AC_Code , WE_Date, SAT, SUN, MON, TUE, WED,
THU, FRI, Notes, General, PO_No, WWL_No, CN_No
*/

/*
SELECT @SQLBase1 ='INSERT INTO WWL_Timesheets (RE_Code,
PR_Code,AC_Code , WE_Date, SAT, SUN, MON, TUE, WED, THU, FRI, Notes,
General, PO_No, WWL_No, CN_No)
VALUES ( '
SELECT @TimesheetCount 1=0

WHILE LEN( @TimesheetDetai ls) > 1
BEGIN
SELECT @SQLComplete1 = @SQLBase1 + LEFT( @TimesheetDetai ls,
Charindex(';', @TimesheetDetai ls) -1) + ')'
EXEC(@SQLComple te1)
SELECT @TimesheetCount 1 = @TimesheetCount 1 + 1
SELECT @TimesheetDetai ls = RIGHT( @TimesheetDetai ls, Len(
@TimesheetDetai ls)-Charindex(';', @TimesheetDetai ls))
END
RETURN
*/

-- Check if insert succeeded.
IF @@ROWCOUNT = @TimesheetCount AND @@ERROR = 0
BEGIN
commit tran
SELECT @RetCode = 1,
@RetMsg = 'Timesheet added successfully.'
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT @RetCode = 0,
@RetMsg = 'Insertion of new Timesheet failed. Timesheet Details
couldn''t be saved.'
END
RETURN
GO

*************** *************** *************** **************
Public Function Test_1()
Dim boolState As Boolean
Dim rstDetails As ADODB.Recordset
Dim strDetails As String
Dim cmd As ADODB.Command
Dim fOK As Boolean
Dim strMsg As String
Dim strOK As String

If gcnn Is Nothing Then
Set gcnn = New ADODB.Connectio n
End If

If gcnn.State = adStateOpen Then
boolState = True
'do nothing
Else
'If Not IsFormOpen("Frm _Login") Then
'DoCmd.OpenForm "Frm_Login" , WindowMode:=acD ialog
'End If
GlobalConnectio n_A = DLookup("[mstrOLEDBConnec t]",
"Refresh_FrontE nd_Access", "Not [mstrOLEDBConnec t] is null")
If GlobalConnectio n_A <> "" Or IsNull(GlobalCo nnection_A) Then
gcnn.Connection String = GlobalConnectio n_A
gcnn.Open
If gcnn.State = adStateOpen Then
boolState = True
Else
boolState = False
End If
'do nothing
End If
End If

'***AIM-Build string from Order Details.
'***AIM-Using ADO Recordset.GetSt ring.

Set rstDetails = New ADODB.Recordset
With rstDetails
.ActiveConnecti on = CurrentProject. Connection
'***AIM-Sort by Access autonumber field, to preserve entry
Timesheet of details.
'***AIM-Open a default read-only, forward-only recordset
.Source = "SELECT '''' & [re_code] & '''' AS RE_Code1,'''' &
[PR_Code]& '''' AS PR_Code1, '''' & [AC_Code] & '''' AS AC_Code1,
Test.CO_Code, Test.CH_Code, 'getdate()' AS TS_LastEdit1, '''' &
FORMAT([WE_Date],'YYYY/MM/DD') & '''' AS WE_Date1, Test.SAT, Test.SUN,
Test.MON, Test.TUE, Test.WED, Test.THU, Test.FRI, '''' & [NOTES] &
'''' AS NOTES1, '''' & [GENERAL] & '''' AS GENERAL1, Test.PO_Number,
Test.WWL_Number , Test.CN_Number, 'getdate()' AS Last_Editby1, '1' AS
User_Confirm1, 'getdate()' AS User_Confirm_Da te1, Test.Appd_By,
Test.Appd_Date, '0' AS Approved1, '' AS a, Test.Concurrenc yID, '' AS b
FROM Test WHERE (((Test.User_Co nfirm)=False)); "
'***AIM-Open a default read-only, forward-only recordset
.Open
'***AIM-Build a string to marshall Timesheet details to the
stored procedure
strDetails = .GetString( _
StringFormat:=a dClipString, _
ColumnDelimeter :=",", _
RowDelimeter:=" ;", _
NullExpr:="NULL ")
.Close
MsgBox strDetails
End With
Set rstDetails = Nothing
'Exit Function
'***AIM-Add Timesheet
Set cmd = New ADODB.Command
With cmd
.ActiveConnecti on = gcnn
.CommandText = "procTimesheetI nsert"
.CommandType = adCmdStoredProc
.Parameters.App end .CreateParamete r("@TimesheetDe tails",
adVarChar, adParamInput, 5000, strDetails)
.Parameters.App end .CreateParamete r("@RetCode", adInteger,
adParamOutput)
.Parameters.App end .CreateParamete r("@RetMsg", adVarChar,
adParamOutput, 100)
.Parameters.App end .CreateParamete r("@TimesheetID ",
adInteger)

.Execute

fOK = .Parameters("@R etCode")
strMsg = .Parameters("@R etMsg")
If fOK Then
Else
strMsg = "Timesheet was not added." & vbCrLf & strMsg
End If
End With
Set cmd = Nothing

End Function