 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / January 2006exporting data to project
I have the database designed by someone with the module which should export data from the database to Microsoft Project. My problem is that it does not work the same way on different systems, results are not the same. On some systems filtering of the data does not work, on others exporting stops if the date value is more than one year in the future. Could someone look into the code of this module and tell me where are the problems coming from. Code below: ************************************* Attribute VB_Name = "modReportToProject" Option Compare Database Option Explicit Sub ReportToProject(Optional lngGroupID As Long, Optional lngSubGroupID As Long) Dim cnn As Object Dim rsExport As Object, rsExportRes As Object Dim pj As Object, apj As Object Dim intCounter As Integer, lngTotalRecords As Long Dim strProjectName As String, strPath As String Dim strCurrentAsset As String, intCreateproject As Integer Dim strGroupName As String Dim blnExists As Boolean On Error GoTo Err_ReportToProject 'set variables Set cnn = CurrentProject.Connection Set rsExport = CreateObject("ADODB.Recordset") Set rsExportRes = CreateObject("ADODB.Recordset") If lngGroupID = 0 Then rsExport.Open "SELECT * FROM qryExportToProject", cnn, 1 rsExportRes.Open "SELECT * FROM qryExportResources", cnn, 1 Else If lngSubGroupID = 0 Then rsExport.Open "SELECT * FROM qryExportToProject WHERE lngGeneralGroupsID = " & lngGroupID, cnn, 1 rsExportRes.Open "SELECT * FROM qryExportResources WHERE [tblGeneralGroups].[lngGeneralGroupsID] = " & lngGroupID, cnn, 1 Else rsExport.Open "SELECT * FROM qryExportToProject WHERE lngAssetGroupID = " & lngSubGroupID, cnn, 1 rsExportRes.Open "SELECT * FROM qryExportResources WHERE lngAssetGroupID = " & lngSubGroupID, cnn, 1 End If End If Set pj = CreateObject("msProject.Application") strPath = CurrentProject.Path & "\" strProjectName = "hire" strProjectName & """ in the path " & strPath & ". " & _ "The project will replace the existing project and take a few minutes. " & Chr(10) & _ "Do you wish to create the project now?", _ buttons:=vbYesNo + vbInformation, _ Title:=APP_TITLE) With pj strGroupName = rsExportRes!strGeneralGroup If lngGroupID = 0 Then For Each apj In .Projects If apj.Name = strProjectName & ".mpp" Then apj.Activate .docClose End If Next Else For Each apj In .Projects If apj.Name = strProjectName & "-" & strGroupName & ".mpp" Then apj.Activate .docClose End If Next End If .Visible = True .Calculation 0 'pjManual .FileOpen Name:=strPath & strProjectName & ".mpt" .ViewApply Name:="Resource &Sheet" 'create the resource table intCounter = 1 rsExportRes.MoveLast rsExportRes.MoveFirst lngTotalRecords = rsExportRes.RecordCount Do While Not rsExportRes.EOF .SetResourceField "Name", rsExportRes!AssetName.Value, , , intCounter .SetResourceField "Initials", rsExportRes!lngAssetCode.Value, , , intCounter .SetResourceField "Code", rsExportRes!lngAssetCode.Value, , , intCounter .SetResourceField "Group", rsExportRes!strGeneralGroup.Value, , , intCounter rsExportRes.MoveNext Forms!Switchboard.Option3.StatusBarText = "Creating Project: Loading Resources " & Format(intCounter / lngTotalRecords, "0%") & " complete!" intCounter = intCounter + 1 Loop rsExportRes.Close 'for each asset in the database .ViewApply "&Gantt Chart" 'Name:="&Gantt Chart" strCurrentAsset = "" intCounter = 1 rsExport.MoveLast lngTotalRecords = rsExport.RecordCount rsExport.MoveFirst Do While Not rsExport.EOF If strCurrentAsset <> rsExport!strName.Value Then .SetTaskField "Name", rsExport!strName.Value, , , intCounter .SetTaskField "Resource Names", rsExport!strName.Value, , , intCounter If .Projects(strProjectName).Tasks(intCounter).OutlineLevel = 2 Then .Projects(strProjectName).Tasks(intCounter).OutlineOutdent End If strCurrentAsset = rsExport!strName.Value intCounter = intCounter + 1 lngTotalRecords = lngTotalRecords + 1 End If If Not IsNull(rsExport!strJobName.Value) Then If rsExport!dteHireStart.Value < .Projects(strProjectName).ProjectStart Then .Projects(strProjectName).ProjectStart = rsExport!dteHireStart.Value End If .SetTaskField "Start", rsExport!dteHireStart.Value, , , intCounter .SetTaskField "Name", rsExport!strJobName.Value, , , intCounter .SetTaskField "Duration", rsExport!dteHireFinish.Value - rsExport!dteHireStart.Value, , , intCounter If Not IsNull(rsExport!intServiceDuration.Value) Then .SetTaskField "Finish10", rsExport!dteHireFinish.Value + rsExport!intServiceDuration.Value, , , intCounter Else .SetTaskField "Finish10", rsExport!dteHireFinish.Value, , , intCounter End If If Not IsNull(rsExport!WShopLoc.Value) Then .SetTaskField "Text10", rsExport!WShopLoc.Value, , , intCounter If rsExport!StatusID = 2 Then 'confirmed .SetTaskField "Flag2", 1, , , intCounter ElseIf rsExport!StatusID = 1 Then 'potential .SetTaskField "Flag1", 1, , , intCounter ElseIf rsExport!StatusID = 3 Then 'go line .SetTaskField "Flag3", 1, , , intCounter End If .SetTaskField "Rollup", "Yes", , , intCounter .SetTaskField "Text1", rsExport!strCustomerName.Value, , , intCounter .SetTaskField "Resource Names", rsExport!strName.Value, , , intCounter If .Projects(strProjectName).Tasks(intCounter).OutlineLevel = 1 Then .Projects(strProjectName).Tasks(intCounter).OutlineIndent End If intCounter = intCounter + 1 End If rsExport.MoveNext Forms!Switchboard.Option3.StatusBarText = "Creating Project: Loading Tasks " & Format(intCounter / lngTotalRecords, "0%") & " complete!" Loop rsExport.Close .Alerts False .Calculation -1 'pjAutomatic .EditGoTo 1, Format(Now(), "d/mm/yyyy hh:mm") If lngGroupID = 0 Then .FileSaveAs Name:=strPath & strProjectName Else .FileSaveAs Name:=strPath & strProjectName & "-" & strGroupName End If End With Forms!Switchboard.Option3.StatusBarText = "" Exit_ReportToProject: Set rsExport = Nothing Set rsExportRes = Nothing cnn.Close Set cnn = Nothing Set pj = Nothing Exit Sub Err_ReportToProject: MsgBox Err.Number & ": " & Err.Description Resume Exit_ReportToProject End Sub ************************************* Thanks for help. Tony, these newsgroups really work best when you ask specific questions, that people can answer "off the cuff". You say that someone else designed it for you. Shouldn't they debug it also? Can't you get them back, to fix it? HTH, TC Hi TC, Thank you for your response. The problem is that the author of this module is not contactable and I need to figure out the problem myself, hopely with help from this newsgroup. That is why I posted my question. I hope that someone will find the error I can not see. It is strange that the same database run on computers with the same version of Windows, Access and Project gives different results. If error will be consistant it may be easier, but it is not and that makes things difficult. Once again thank you for your response. Regards, Tony > Tony, these newsgroups really work best when you ask specific > questions, that people can answer "off the cuff". [quoted text clipped - 4 lines] > HTH, > TC Ok. The problem, from our viewpoint, is that the error is not a simple one (like a compile error) that we could help you quickly with. The problem, from our viewpoint, is that the code does not match your expectations of how it should work. But we don't /know/ those expectations, so it will be very difficult for us to say what might be wrong with the code. Let's start here: (1) What do you mean when you say that on some systems, "filtering of the data does not work"? What /specifically/ does or does not happen to caus ou to say that? Can you give some specific examples (including data values) that illustrate this problem (whatever it is)? (2) Similarly, what exactly do you mean when you say that "exporting stops if the date value is more than one year in the future"? What specifically does or does not happen? What is a specific date which causes that to happen or not happen? HTH, TC Thank you for your response again. To answer your questions: 1. Then number of exported items is different and the difference is large. On one system it is exporting 290 records while on the second it is exporting 807 records. On first system it is exporting records as selected in selection form while on second system it is exporting all records, selection is ignored. 2. On one of the systems, the ones which export less records, exporting stops once the date selected is more than year in advance. On the other systems changing of the dates in some records does not have any impact on results. I have tried to do filtering in the query outside this module but this was giving me errors in this module in the If area with rsExport.Open. At the moment I am going to try to setup the query which will prepare the data and once it will be right I will try to export it to Project. I need to replace this trouble If area. What I am looking for is what is the correct syntax to replace the lines: rsExport.Open "SELECT * FROM qryExportToProject", cnn, 1 so that I will just open the modified qryExportToProject. Should I just use rsExpor.Open without any parameters ? Thank you for your help. Regards, Tony > Ok. The problem, from our viewpoint, is that the error is not a simple > one (like a compile error) that we could help you quickly with. The [quoted text clipped - 17 lines] > HTH, > TC You say that it works differently on different systems. Are you /absolutely positive/ that you are running the /exactly same data/ on each PC? The only way that you could do that, would be to create two identical copies of the database, and run one of them on each PC. It's no good to run the database on one PC, then take it to another PC and run it on that one. The first run might change the data, causing a different result on the next run. Tony, I really think that this will be too hard to do, over the net, for free. Do you have any local Access people that could look at it for you? HTH, TC Thank you once again for your responses. Yes, the data is the same, versions of Windows, Access and Projects are the same. But I have not checked the systems settings yet. It could be that the date formats are different and this is the source of the different results. I will check all and should find what is happening. I have already fixed many bugs in this database and I hope I will manage to fix this one too. I was just hoping that maybe someone had similar problem / experience in the future and this will point me to the right direction. Looks like I have to spend more time and check all settings. Thanks for help. Tony > You say that it works differently on different systems. Are you > /absolutely positive/ that you are running the /exactly same data/ on [quoted text clipped - 12 lines] > HTH, > TC Yes - dates can be tricky. Maybe that could be it! When you reference dates in SQL statements, they have to be enclosed in cross-hatches, for example: WHERE DateDue=#6/9/2006# The trick is, that these "cross-hatched" dates must be specified in American (month/day/year) format, << regardless of the PC's locale settings >>. So the example date above, would be taken as June 9 2006 - not 6 September 2006 - << regardless of the PCs locale settings. >> Of course, you do not often have fixed dates, in SQL statements. But you often /do/ have dates as parameters, or obtained from a form control, or a global variable, or whatever. Or, you might build-up an SQL string at runtime. So things like this can be very risky, unless you know /for certain/, how the date will be formatted: sql = sql & "#" & me![txtDateDue] & "#" HTH, TC Purely by coincidence, I just saw this thread; see if it helps: http://groups.google.com/group/microsoft.public.access/msg/d98bf8059a444e86 HTH, TC Hi TC, Trying to solve my problem I am trying to put filtering into the queries before exporting results to MS Project. But this is giving me the error: -2147217904 No value given for one or more required parameters in the line: rsExport.Open "SELECT * FROM qryExportToProject", cnn, 1 When I will run this query it has correct data. What parameters are missing, how I should modify this line. When I remove filters in query error dissapears. Regards, Tony > Purely by coincidence, I just saw this thread; see if it helps: > > http://groups.google.com/group/microsoft.public.access/msg/d98bf8059a444e86 > > HTH, > TC Sorry, I only just saw your reply. If you're still around, can you show me the code pertaining to rsExport? How do you declare it & put something into it? You say that you are "filtering", but there is no "filtering" evident in the statement you've shown. TC [MVP Access] Hi TC, thank you for your reply again. As you could see in my original message the filtering of the data should be done by: ***************************************** If lngGroupID = 0 Then rsExport.Open "SELECT * FROM qryExportToProject", cnn, 1 rsExportRes.Open "SELECT * FROM qryExportResources", cnn, 1 Else If lngSubGroupID = 0 Then rsExport.Open "SELECT * FROM qryExportToProject WHERE lngGeneralGroupsID = " & lngGroupID, cnn, 1 rsExportRes.Open "SELECT * FROM qryExportResources WHERE [tblGeneralGroups].[lngGeneralGroupsID] = " & lngGroupID, cnn, 1 Else rsExport.Open "SELECT * FROM qryExportToProject WHERE lngAssetGroupID = " & lngSubGroupID, cnn, 1 rsExportRes.Open "SELECT * FROM qryExportResources WHERE lngAssetGroupID = " & lngSubGroupID, cnn, 1 End If End If *************************************** lngGroupID and lngSubGroupID should be used for selection and filtering of rsEsport and rsExportRes. As I have indicated I have tried to put filtering into the source queries instead but this was not working too. I am still trying. Regards, > Sorry, I only just saw your reply. If you're still around, can you show > me the code pertaining to rsExport? How do you declare it & put [quoted text clipped - 4 lines] > > TC [MVP Access] > Hi TC, thank you for your reply again. No probs. But unfortunately I am quickly running out of ideas! Also I do not know ADODB. But let's take one more try in any case. > rsExport.Open "SELECT * FROM qryExportToProject", cnn, 1 > rsExportRes.Open "SELECT * FROM qryExportResources", cnn, 1 Those look ok to me. But you say that you are getting error -2147217904 "No value given for one or more required parameters" on the first of those statements. So, does qryExportToProject require parameters? If so, shouldn't you provide the relevant parameter value, using the appropriate ADODB syntax? Maybe a "parameters" method, or somesuch? > rsExport.Open "SELECT * FROM qryExportToProject WHERE > lngGeneralGroupsID = " & lngGroupID, cnn, 1 That looks ok to me. > rsExportRes.Open "SELECT * FROM qryExportResources WHERE > [tblGeneralGroups].[lngGeneralGroupsID] = " & lngGroupID, cnn, 1 Are you sure that this query does actuall expose the table/alias name "tblGeneralGroups"? > rsExport.Open "SELECT * FROM qryExportToProject WHERE > lngAssetGroupID = " & lngSubGroupID, cnn, 1 > rsExportRes.Open "SELECT * FROM qryExportResources WHERE > lngAssetGroupID = " & lngSubGroupID, cnn, 1 Those look ok to me. HTH, TC [MVP Access] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.