[AccessD] Importing data using a Class..Who can help/review?

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".
***********************************************************************************




More information about the AccessD mailing list