[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

Conversion MS Access 2003 To 2010 - VB Code

Hi Experts,
I am in the process of converting an Access 2003 Application to 2010.

Environment OS is Windows Pro 7. 64 Bit, Office 2010 Pro.

I have received from you the following 2010 code for DB management that I am using to replace the existing 2003 code.
Dim DB_nnn As Database
Set DB_nnn = CurrentDb
Dim RS_00_PROCESS_CTRL_nnn As DAO.Recordset
Dim CMD_00_PROCESS_CTRL_nnn As String
[CMD_00_PROCESS_CTRL_nnn] = "SELECT Key_1, Field_1 " & _
                            "FROM 00_PROCESS_CTRL " & _
                            "WHERE Key_1 = '" & PARM_nnn & "';"
Set RS_00_PROCESS_CTRL_nnn = DB_nnn.OpenRecordset([CMD_00_PROCESS_CTRL_nnn], dbOpenDynaset)
[WRK_PRIMARY_DB_LOCATION_nnn] = RS_00_PROCESS_CTRL_nnn!Field_1
RS_00_PROCESS_CTRL_nnn.Close
Set RS_00_PROCESS_CTRL_nnn = Nothing
DB_nnn.Close
Set DB_nnn = Nothing

Open in new window

I currently have the attached 2003 code that is running without errors.  However I would like to adjust it as necessary to bring it up to 2010 standards.  i.e. Like the code above.

This Event creates a MS Excel Workbook containing a Worksheet for each Table being exported.

Could you please advise what needs to be changed?

Thanks.
Bob C.
File-Transfer-Original-Code.txt
0
Bob_Collison
Asked:
Bob_Collison
  • 7
  • 5
  • 4
  • +4
13 Solutions
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>Could you please advise what needs to be changed?
Basically nothing in the code (as long as the current one works).  You will have to change a reference to the newer excel path but that is with a compile error.
0
 
Bob_CollisonAuthor Commented:
Hi Andy,

The three lines below are those that I figured should be changed to use the DAO instead of ADODB and 'Set Connection...' as with Access 2010 Microsoft are going back to the DAO terminology.

Dim RS_10_MEMBER_MSTR_XFER_1100 As ADODB.Recordset
Set Connection_0100 = CurrentProject.Connection
Set RS_10_MEMBER_MSTR_XFER_1100 = New ADODB.Recordset

Also I'm not sure what you are referring to regarding the 'to change a reference to the newer excel path but that is with a compile error'.  Could you please clarify?

Thanks.
Bob C.
0
 
Rey Obrero (Capricorn1)Commented:
try this
Dim DB_nnn As Database
Set DB_nnn = CurrentDb
Dim RS_00_PROCESS_CTRL_nnn As DAO.Recordset
Dim CMD_00_PROCESS_CTRL_nnn As String
CMD_00_PROCESS_CTRL_nnn = "SELECT Key_1, Field_1 " & _
                            "FROM 00_PROCESS_CTRL " & _
                            "WHERE Key_1 = '" & PARM_nnn & "';"
Set RS_00_PROCESS_CTRL_nnn = DB_nnn.OpenRecordset(CMD_00_PROCESS_CTRL_nnn, dbOpenDynaset)

If Not RS_00_PROCESS_CTRL_nnn.EOF Then

WRK_PRIMARY_DB_LOCATION_nnn = RS_00_PROCESS_CTRL_nnn!Field_1

end if

RS_00_PROCESS_CTRL_nnn.Close
Set RS_00_PROCESS_CTRL_nnn = Nothing
DB_nnn.Close
Set DB_nnn = Nothing

Open in new window


also, you need to define  "WRK_PRIMARY_DB_LOCATION_nnn"
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Dale FyeCommented:
1.  First off, I'm wondering why you used 13 lines of code to get the value of a field in a table when you could have used one:

WRK_Primary_DB_Location_nnn = DLOOKUP("Field_1", "00_Process_Ctrl", "[Key_1] = " & Param_nnn)

I'm also not sure why you are wrapping whta appear to be variables in [ ].

2. Personally, rather than repeating sections of code for different recordsets and worksheets, I would probably create an array that contains the names of the tables or queries that you want to write to Excel, and loop through the array.  

3.  As far as opening the recordset, it is very simple with DAO:

Dim rs as DAO.Recordset
set rs = currentdb.Openrecordset("Table or Query Name", dbOpenDynaset, dbFailonError)

rs.close
set rs = nothing
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
Microsoft never dropped DAO, and there is nothing against using ADO in 2010. In fact, it is DAO that starts to be deprecated and can even cause problems on 64-bit Windows if you look at https://msdn.microsoft.com/en-us/library/office/ff965871%28v=office.14%29.aspx#DataProgrammingWithAccess2010_DeprecatedMethods.

DAO has always and still offers a little more possibilities, because it is designed specifically for Access.

ADO being a universal data access technology does has a few limits with Access, but makes the code more easily portable should you even move you data to another database.

If your code works, there is no reason to change it.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>The three lines below are those that I figured should be changed to use the DAO instead of ADODB

I'm puzzled.  The code you posted is using DAO and converting from DAO to ADODB means a lot of changes as they do not behave the same way, even functionality is missing.  I would recommend staying with DAO.

re references:ReferencesExtras menu, then references (verweise in german - my edition), then select the Excel version from the list.
0
 
Bob_CollisonAuthor Commented:
Hi Experts,

Thanks for all your comments.  There is a lot for me to respond to.

 To clarify my original question.  I'm trying to convert from ADO to DAO.  Therefore the focus of my question is the statements relating to the use of ADODB.  I don't have any questions regarding the code that I have using DAO.  I just provided it as a reference to what I have already converted to as per previous EE advice.  Sorry if I didn't make this clear.

Am I correct that I can use DAO and ADODB without problems?  Some of my original ADODB Code seems to cause problems after conversion although the code I attached doesn't.

The multiple lines of code to extract the data from the table are because I have intermittent problems using DLOOKUP.

I haven't used an array in place of the five sets of code because I am nor very good at programming arrays.  Something for me to work on.

With regard to the References.  I'm using the following in the sequence provided.
- Visual Basic For Applications.
- Microsoft Access 14.0 Object Library.
- OLE Automation.
- Microsoft ActiveX Data Objects 2.5 Library.
- ctv OLE Control Module.
- Microsoft Office 14.0 Office Library.
- Microsoft Excel 14.0 Library.
- Microsoft Office 14.0 Access database engine Object Library
- Microsoft Visual Basic for Applications Extensibility 5.3

Is there anything that should be added / deleted, re-ordered?  e.g. Should I replace the '14.0' Versions with '15.0' Versions?

Thanks.
Bob C.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
The '15' is Office 2013
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
ActiveX Data Objects 2.5 is very old. Access 2003 already used 2.8. I have only Access 2007 in the environment I am today, and it uses ADO 6.0. This might be why your original ADODB code sometimes does not work.

As for DLookup, I have been using Access since version 1.0, and it never gave me problem. All it does under the hood is create an SQL statement. When you do get a problem, it is usually because you did not specify the criteria correctly or have data that can cause problems such as a single quote in a name (ex: O'Connell)
0
 
Bob_CollisonAuthor Commented:
Hi James,
Thanks for the References information.  I will replace the Microsoft ActiveX Data Objects 2.5 Library with Microsoft ActiveX Data Objects 6.1 Library which is what is listed in References.

I will have another go at using DLOOKUP as it certainly is much simpler if I can get the syntax correct.  I do have problems with syntax since I'm not a trained programmer (I have learned it myself).

Thanks.
Bob C.
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
If you have problems with one of your DLookup, simply post the code, as well as the values that you need to insert in the criteria. We should be able to help.
0
 
Bob_CollisonAuthor Commented:
Hi James,
Thanks for the offer.
Bob C.
0
 
Dale FyeCommented:
Bob,

When doing DLOOKUPs, I like to use the following syntax

Dim strCriteria as string

strCriteria = "[SomeNumericField] = " & SomeNumericValue
'or for text
strCriteria = "[SomeTextField] = '" & SomeTextValue & "'"
debug.print strCriteria
SomeValue = DLOOKUP("[SomeField]", "SomeTableOrQueryName", strCriteria)

Open in new window


With this syntax, I get to see what the criteria string was if it generates an error.  Too many people like to build the criteria string on the fly and have no way of testing that value, and for some reason, people cannot seem to remember that text strings must be bracketed with either single (my example) or double quotes.
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
But you have to be careful. If the data passed in a as SomeTextValue does contain quotes, such as the O'Connell example I gave sooner, the single quote included in SomeTextValue will fool the interpreter as being the end of the value and cause the command to fail. This is typical of programmers who say "I have intermittent problems using DLOOKUP" as appeared in the original question. Something similar can happen if you use double quotes as a delimiter and a double quote appears in the text value.
0
 
Dale FyeCommented:
I actually have a function i use to account for the embedded single or double quotes.  Do a search on fnWrap here in EE

Sent from my iPad
0
 
aikimarkCommented:
my rules of thumb:
1. Always use single quote characters as your string delimiters, not apostrophe characters.
Examples:
Where MyField=""" & strVariable & """"

Open in new window

Where MyField=" & Chr(34) & strVariable & Chr(34)

Open in new window


Where MyField=" & QuotedString(strVariable)

Open in new window

Function QuotedString(ByVal parmString As String) As String
    Const cQuoteChar As String = """"
    QuotedString = cQuoteChar & parmString & cQuoteChar
    'same as QuotedString = Chr(34) & parmString & Chr(34)
End Function

Open in new window


2. Use the Replace() function to change any embedded quote characters to double-quote strings.
Example:
Function SafeString(ByVal parmString As String) As String
    SafeString = Replace(parmString, """", """""")
End Function

Open in new window


I would expect Dale's wrapper function to be some combination of these two functions.
Example:
Function SaveSearchLiteral(ByVal parmString As String) As String
    SaveSearchLiteral = QuotedString(SafeString(parmString))
End Function

Open in new window


Note: The regexp object could also have been used, but Replace() is faster for this trivial task
0
 
Bob_CollisonAuthor Commented:
Hi Experts,
To sum up.  I use Access as the DBMS exclusively.  i.e. No others.  Therefore based on previous EE discussions I opted to go with DAO exclusively for simplification.  In this regard I don't believe that my original question has been answered.

Although the information regarding References was not asked for it is certainly relevant and appreciated.  I have made changes accordingly.

Also the DLOOKUP information was not part of the original question but is welcomed and as I have mentioned I will try to get educated in its use as I can see the advantage.

Therefore what is left is the question regarding whether there is a DAO equivalent syntax to my code in the attached file with specific reference to the statements below that are contained within it or should / must I continue using the existing ADODB syntax?
Dim Connection_0100 As ADODB.Connection
Dim RS_10_MEMBER_MSTR_XFER_1100 As ADODB.Recordset
Set Connection_0100 = CurrentProject.Connection
Set RS_10_MEMBER_MSTR_XFER_1100 = New ADODB.Recordset

Thanks.
Bob C.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Therefore what is left is the question regarding whether there is a DAO equivalent syntax to my code in the attached file with specific reference to the statements below that are contained within it or should / must I continue using the existing ADODB syntax?>>

  You don't need to continue to use ADO, but there are many cases where there is simply not a DAO equivalent because the two are just different.  In other words, it's not a one for one statement swap.  DAO for example has no connection or command object.

Dale in this comment:

http://www.experts-exchange.com/Programming/Microsoft_Development/Q_28603192.html#a40569129

 Showed you what an open on a DAO recordset would look like:

Dim rs as DAO.Recordset
set rs = currentdb.Openrecordset("Table or Query Name", dbOpenDynaset, dbFailonError)

rs.close
set rs = nothing

 and that's what would replace all your ADO code.  

 and there is nothing saying you can't keep the ADO code; it will continue to work.   DAO however is faster for dealing with JET/ACE based DB's

No points please.

Jim.
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
You do not require a connection in DAO, as I said originally (looking at the code you posted in the question) you would need to change very little.  Looping is the same, accessing field contents is the same....

dim db As Database
dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset(put your query here)
    rs.MoveFirst
    While Not rs.EOF
        xxx = rs.Fields(0)  'first field in the recordset
        yyy = rs.Fields(1) 'second field
        rs.MoveNext
    Wend
set rs = nothing
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
ps.  As I mentioned in an earlier comment there are considerable differences in the functionality between ADO and DAO (some things are not possible in one but allowed in the other) and how they operate in the background, but in general you can use them in the same way.
0
 
Bob_CollisonAuthor Commented:
Hi Experts,
I am satisfied with all of your comments however I have just spent 25 minutes trying to award points and offer comments only to have it all wiped out when I tried to get some help.  I find the restrictions placed on the awarding of points very difficult to deal with.
Suggestions so I can complete this?
Thanks.
Bob C.
0
 
Bob_CollisonAuthor Commented:
Hi Experts,
The original question was answered plus received great additional insight into DLOOKUP which I will put to good use.
Thanks.
Bob C.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 7
  • 5
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now