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.
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
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 = NothingThis 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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
ASKER
Hi James,
Thanks for the offer.
Bob C.
Thanks for the offer.
Bob C.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<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:
https://www.experts-exchange.com/questions/28603192/Conversion-MS-Access-2003-To-2010-VB-Code.html?anchorAnswerId=40569129#a40569129
Showed you what an open on a DAO recordset would look like:
Dim rs as DAO.Recordset
set rs = currentdb.Openrecordset("T
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.
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:
https://www.experts-exchange.com/questions/28603192/Conversion-MS-Access-2003-To-2010-VB-Code.html?anchorAnswerId=40569129#a40569129
Showed you what an open on a DAO recordset would look like:
Dim rs as DAO.Recordset
set rs = currentdb.Openrecordset("T
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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.
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.
ASKER
Hi Experts,
The original question was answered plus received great additional insight into DLOOKUP which I will put to good use.
Thanks.
Bob C.
The original question was answered plus received great additional insight into DLOOKUP which I will put to good use.
Thanks.
Bob C.
ASKER
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_110
Set Connection_0100 = CurrentProject.Connection
Set RS_10_MEMBER_MSTR_XFER_110
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.