473,573 Members | 4,498 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
Nov 12 '05 #1
0 4479

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1520
by: Big Time | last post by:
I have an Access 2000 DB that I've been considering upsizing to SQL Server 2K. I'm wondering if anyone can share their experiences in upsizing and let me know of any tips or pratfalls. I've been playing around with the upsize wizard however not all of my tables have properly converted. None of the primary keys transfer (I understand this is...
0
1803
by: ImraneA | last post by:
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.Using Upsizing wizard - will either work or not. For me it did not. Why ?
1
2083
by: John | last post by:
Hi I am using the upsizing wizard to move a table from access 2000 to sql server. The table does not get exported and all I am getting is a 'Table was skipped, or export failed' error in the upsizing report. What is going on? Thanks Regards
1
2093
by: Calum Chisholm | last post by:
I'm looking to call the upsizing wizard from within a VBA function, preferably automating the entire upsizing process. Has anyone achieved this under Access 2003? The best I've managed so far is to .Execute its CommandBar control, but this is a pretty messy approach and still requires input on the part of the user. Anyone know of a...
49
14297
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The application is relatively big: around 200 tables, 200 forms and sub-forms, 150 queries and 150 repports, 5GB of data (SQL Server 2000), 40 users. ...
12
1827
by: John | last post by:
Hi We have a front end/back end type access app. We would like to upsize the app to sql server but can not re-write the whole app immediately. Is it feasible to just upsize the backend (data part) only to sql server using upsizing wizard without any drastic effect on the performance of the front end access app (which would link to the...
3
2027
by: Dave Stone | last post by:
This question appeared years ago in the context of Acc2K and SQL Server 7, but no replies were posted. HOWEVER!! It still seems to be a problem with Acc XP and SQL Server 2000. Surely someone has a fix 6 years down the line... I used the Upsizing Wizard to build a SQL database from an Access back-end and link it to the Access f/e. All the...
3
1857
by: Devonish | last post by:
I am planning to convert an existing Access database which has a back end (data tables and relationships only) on a server and a copy of the front end (form, queries, reports) on each of about a dozen workstations. I intend to convert the back end to SqlServer and wish to use the upsizing wizard. I am acquiring SSW Upsizing Pro! 2000...
7
3021
by: JM | last post by:
I'm using Access 2003 to attempt an upsize to SQL Server 2000. The Upsizing Wizard is giving me tons of problems with error messages like: 1. "Object is invalid. Extended properties not permitted on . . ." 2. "No primary or candidate keys in referenced table . . . that match referencing column list in foreign key . . . Could not...
0
7978
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8167
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7730
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8028
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5252
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3692
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2164
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1263
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
987
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.