Solved

Conversion MS Access 2003 To 2010 - VB Code

Posted on 2015-01-24
24
279 Views
Last Modified: 2015-02-04
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
Comment
Question by:Bob_Collison
  • 7
  • 5
  • 4
  • +4
24 Comments
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 80 total points
ID: 40568909
>>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
 

Author Comment

by:Bob_Collison
ID: 40569084
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 20 total points
ID: 40569115
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 160 total points
ID: 40569129
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
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 160 total points
ID: 40569130
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
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 80 total points
ID: 40569135
>>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
 

Author Comment

by:Bob_Collison
ID: 40569185
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
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 80 total points
ID: 40569293
The '15' is Office 2013
0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 160 total points
ID: 40569354
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
 

Author Comment

by:Bob_Collison
ID: 40569407
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
 
LVL 40
ID: 40569430
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Bob_Collison
ID: 40569442
Hi James,
Thanks for the offer.
Bob C.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 160 total points
ID: 40569815
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
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 160 total points
ID: 40569898
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 160 total points
ID: 40569922
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
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 20 total points
ID: 40569972
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
 

Accepted Solution

by:
Bob_Collison earned 0 total points
ID: 40579684
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
 
LVL 57
ID: 40579718
<<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
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 80 total points
ID: 40579732
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
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40579740
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
 

Author Comment

by:Bob_Collison
ID: 40579811
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
 

Author Closing Comment

by:Bob_Collison
ID: 40588210
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now