Jim DeMarco
Jdemarco at hudsonhealthplan.org
Tue Mar 30 08:53:01 CST 2004
Sorry there's an error in the code I posted. In the SaveLine method this:
With rstInterfaceData
.ActiveConnection = oCnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM tblInterfaceData"
.Open
.AddNew
!InterfaceID = intInterfaceID
' ...code omitted
should be:
With rstInterfaceData
.ActiveConnection = m_oCnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM tblInterfaceData"
.Open
.AddNew
!InterfaceID = m_intInterfaceID
' ...code omitted
...forgot to refer to the module level variables!
Jim DeMarco
-----Original Message-----
From: Jim DeMarco
Sent: Tuesday, March 30, 2004 9:36 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Importing data using a Class..Who can
help/review?
Sander,
I took a quick look at this and I do see one thing I'd change. Rather than pass the connection and InterfaceID to every call of the cInterface.SaveLine method why not create an Init method in cInterface where you can pass in the connection and ID one time only before the loop? So your ImportInterface code might look like this when you're done:
<snip>
cnnConnection.BeginTrans
cInterface.Init cnnConnection, intInterfaceID
Do While Not objTS.AtEndOfStream
'strCurrentLine = objTS.ReadLine
blnSaveLineOK = cInterface.SaveLine(intLineID, objTS.ReadLine)
intLineID = intLineID + 1
Loop
</snip>
And your class would have a couple of module level variables to hold the connection and InterfaceID:
<snip2>
Dim m_oCnn as ADODB.Connection
Dim m_intInterfaceID as Integer
Public Sub Init(Connection as ADODB.Connection, InterfaceID as Integer)
Set m_oCnn = Connection
m_intInterfaceID = InterfaceID
End Sub
Function SaveLine(intLineID As Integer, strLine As String) As Boolean
Dim rstInterfaceData As ADODB.Recordset
On Error GoTo SaveLine_Error
Set rstInterfaceData = New ADODB.Recordset
With rstInterfaceData
.ActiveConnection = oCnn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM tblInterfaceData"
.Open
.AddNew
!InterfaceID = intInterfaceID
!InterfaceDate = Format(Now)
!InterfaceLine = intLineID
!InterfaceData = strLine
.Update
End With
' ...remaining code omitted for space
</snip2>
HTH
Jim DeMarco
Director Application Development
Hudson Health Plan
-----Original Message-----
From: S D [mailto:accessd667 at yahoo.com]
Sent: Tuesday, March 30, 2004 2:38 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Importing data using a Class..Who can
help/review?
OK, I wanted to add a minidb but a message has a max size of 30KB...
I've added the code I use in the following order:
Form
Module
Class
I've got the following questions:
Q1: I'm calling the method SaveLine via a function ImportInterface. I don't think that this is very good programming.
How can I improve this?
Q2: I'm not using properties. Do I need them? For what and why?
Q3: If anyone has more tips...
Form Code
Private Sub cmdReadFile_Click()
Dim intInterfaceID As Integer
intInterfaceID = cboImport
Call ImportInterface(cboImport, "D:\Temp Projecten\Access\Interface.dat")
End Sub
Module
'=========================================================================================
' Function Name : ImportInterface
' Parameters : intInterfaceID, strFilename
' Purpose : inserts every line of the interface into tblData
' Assumptions : ---
' Uses : ---
' Created : 2004-03-29 07:56, SaDe
' Modifications :
'=========================================================================================
Public Sub ImportInterface(intInterfaceID As Integer, strFilename As String)
Dim objFSO As FileSystemObject
Dim objTS As TextStream
Dim strCurrentLine As String
Dim strSQL As String
Dim dtmCurrentDate As Date
Dim intLineID As Integer
Dim cInterface As cInterface
Dim cnnConnection As ADODB.Connection
Dim blnSaveLineOK As Boolean
On Error GoTo ImportInterface_Error
Set cnnConnection = CurrentProject.Connection
Set objFSO = New FileSystemObject
Set objTS = objFSO.OpenTextFile(strFilename)
Set cInterface = New cInterface
blnSaveLineOK = False
intLineID = 1
cnnConnection.BeginTrans
Do While Not objTS.AtEndOfStream
'strCurrentLine = objTS.ReadLine
blnSaveLineOK = cInterface.SaveLine(cnnConnection, intInterfaceID, intLineID, objTS.ReadLine)
intLineID = intLineID + 1
Loop
'Check if method executed correct
If blnSaveLineOK Then
cnnConnection.CommitTrans
Call MsgBox("Interface imported!", vbInformation)
Else
cnnConnection.RollbackTrans
Call MsgBox("An error occured!", vbCritical)
End If
Set cnnConnection = Nothing
Exit Sub
ImportInterface_Exit:
' Collect your garbage here
cnnConnection.RollbackTrans
Set cnnConnection = Nothing
Exit Sub
ImportInterface_Error:
' Collect your garbage here
MsgBox "An Error Occured!!!" & Err.Number & ";" & Err.Description
End Sub
class cInterface
Function SaveLine(cnnConnection As ADODB.Connection, intInterfaceID As Integer, intLineID As Integer, strLine As String) As Boolean
Dim rstInterfaceData As ADODB.Recordset
On Error GoTo SaveLine_Error
Set rstInterfaceData = New ADODB.Recordset
With rstInterfaceData
.ActiveConnection = cnnConnection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Source = "SELECT * FROM tblInterfaceData"
.Open
.AddNew
!InterfaceID = intInterfaceID
!InterfaceDate = Format(Now)
!InterfaceLine = intLineID
!InterfaceData = strLine
.Update
End With
Set rstInterfaceData = Nothing
'Set cnnConnection = Nothing
SaveLine = True
SaveLine_Exit:
' Collect your garbage here
Set rstInterfaceData = Nothing
'Set cnnConnection = Nothing
Exit Function
SaveLine_Error:
' Collect your garbage here
SaveLine = False
Call MsgBox(Err.Number & vbCrLf & _
Err.Description & vbCrLf & _
Err.Source & vbCrLf & _
"Newest.cInterface.SaveLine", vbCritical)
Resume SaveLine_Exit
End Function
rsmethurst at uk.ey.com wrote:
Sure Sander...
I will.
Cheers
Ryan
S D
Sent by: accessd-bounces at databaseadvisors.com
29/03/2004 14:46
Please respond to Access Developers discussion and problem solving
To: accessd
cc:
Subject: [AccessD] Importing data using a Class..Who can help/review?
Hi group,
I'm building a little app that is able to import textfiles. I want to do
this using a class. I've made a start and it works! Does anybody have the
time/interest to shoot at it?
It's very basic.
I've got:
a class called: cInterface
a method called: SaveLine
TIA
Sander
---------------------------------
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
This e-mail and any attachment are confidential and contain proprietary information, some or all of which may be legally privileged. It is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient, please notify the author immediately by telephone or by replying to this e-mail, and then delete all copies of the e-mail on your system. If you are not the intended recipient, you must not use, disclose, distribute, copy, print or rely on this e-mail.
Whilst we have taken reasonable precautions to ensure that this e-mail and any attachment has been checked for viruses, we cannot guarantee that they are virus free and we cannot accept liability for any damage sustained as a result of software viruses. We would advise that you carry out your own virus checks, especially before opening an attachment.
The UK firm Ernst & Young LLP is a limited liability partnership registered in England and Wales with registered number OC300001 and is a member practice of Ernst & Young Global. A list of members? names is available for inspection at 1 More London Place, London, SE1 2AF, the firm?s principal place of business and its registered office.
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
---------------------------------
Do you Yahoo!?
Yahoo! Finance Tax Center - File online. File on time.
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
***********************************************************************************
"This electronic message is intended to be for the use only of the named recipient, and may contain information from Hudson Health Plan (HHP) that is confidential or privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited. If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message. Thank You".
***********************************************************************************
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
***********************************************************************************
"This electronic message is intended to be for the use only of the named recipient, and may contain information from Hudson Health Plan (HHP) that is confidential or privileged. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited. If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message. Thank You".
***********************************************************************************