MS Access Progress Bar


Hi guys,
I know how to use progress bar when you have more than 1 command or recordset (you know the number or records before you start progress bar). My problem is that I'm not looping through a recordset, I'm running a canned
query (max 2 min.) - 1 command. No code appears to execute while the query is running, so I don't know how to make the progress meter increase. If I just start it before the query is executed, all I can do is move the meter to 100% after the query finishes executing. That's why I tried the timer event, but it doesn't run, either. I have put this problem in different forums, but no answer. I don't believe that this is not possible.

Thanks,
dj


Sponsored Links:



hello,

how to show report and forms in ms access menu bar,
all my reports and data entry form should show in access menu bar how i can do it?




Hello,

I have created a Form (attached) that has two command buttons that copies databases to a different location. The process takes a while to complete, so I thought a progress/status bar would be appropriate so that users know that the process is occurring behind the Form.

I tried creating a progress/status bar to no avail. Would someone please help me do this? I attached the Form I use and hope that a code exists that I (or you) could add to fulfill this request.

Thank you very much!

Tommy Attached Files DB_Copy_Command.zip (20.7 KB, 5 views) Reply With Quote 06-09-2011, 11:57 AM #2 orange VIP Windows XP Access 2003 Join Date Sep 2009 Location Ottawa, Ontario, Canada Posts 4,207 I search Google looking for MS Access Progress Bar on Form

http://www.granite.ab.ca/access/progressbar.htm




Hi all,

I've managed to get my hands on some code examples for progress bars but they all rely on a preset countdown value. For example the value of 60000 will show a progress bar that takes around a minute to countdown.

I am wondering if there is a way to invoke system based values. I am asking because I have a VBA code that takes around 10-15 seconds to run (calculations) depending on machine capabilities and network speed. Is there a way to show a progress bar telling users % of the current calculation process?

Thank you.




Hi again all! Before I ask my question let me just say you folks have always been kind and helpful, and I appreciate your time. I did search this site and several others via google and I found some progressbar code that I Frankensteined onto my own Access project. It works...the problem is the user can't see the stupid thing until it's finished.
This is the way I have it set up:
I have a form called Progress, it is non-modal + non-popup, and has just two objects on it, Label1 and Label2.

Under MS Access Class Objects>Form_Progress:
*Note - I also tried this on the Form_Open and Form_Load events with the same negative result.

Private Sub Form_Activate()
Me.Label1.Height = 600
Me.Label1.Caption = "0% Complete"
Me.Label1.Width = 0
Me.Label1.BackColor = wdColorBlue
Me.Label2.Caption = "Checking Directories"
Me.Repaint
DoEvents
DoCmd.SetWarnings False
Call ListFiles
DoCmd.SetWarnings True
End Sub

This obviously is triggered whenever the Progress Form is opened, which I have it set to do on Startup. The procedures on App startup include identifying all files in all subdirectories of a given directory, inserting them into a Temp Table (temptable2) as records, then comparing temptable2 to temptable (the temp table from the last time the procedure was run). For any files that are there now that were not there last time, a function is called that will import several fields from the target excel files and insert that data into a data table table as a new record. For files that were there before, but the saved FileDateTime is prior to the current FileDateTime, another function is called that will update the records with the data that has changed.This is all triggered by my main function, Listfiles().
There are 5 main steps and I've worked out how I want to increment the progressbar. Listfiles() calls the function Increment() at these 5 points and increments the bar by preset %s. For example, step 1 is search the directory and append any new datafiles, that's 20% of the overall task. 2 is to count and identify the new files, that's 10%...3 is to import new records into my data table (not the temp table), that's 30%, 4 is check files that were there before but they have been modified since then (10%), and 5 is update the records in the data table with the new data from the modified source (30%). So what I am trying to say is, after each of those steps, I call the function Increment() and it has 3 arguments...the first is the % complete, the second is the text message (eg. "Searching Directories..."), and the third is the distance I want the box to increase.

Here is the function that increments the bar:
Function Increment(sPercentComplete As Single, _
sDescription As String, sInterval As Double)
'update label1 with % done, label2 with message, Expand label1 horizontally
On Error Resume Next
Dim iPercentIncrement As Integer, curWid As Double
iPercentIncrement = Format(sPercentComplete, "#")
curWid = Forms!progress.Label1.Width
curWid = curWid + sInterval
Forms!progress.Label1.Width = Format(curWid, "#.##")
Forms!progress.Label1.Caption = sPercentComplete & "%"
Forms!progress.Label2.Caption = sDescription
Forms!progress.Repaint
DoEvents
End Function

And here is the main function that calls it:
*Note that the functions that append and update real data records have been commented out during testing.
Function ListFiles()
Dim varFoundFile As Variant
Dim lngFile As Long
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim mySQL As String, fDate As Date, obj As Object
Dim interval As Double, i As Integer, x As Integer
Set conn = CurrentProject.Connection
rs.activeconnection = conn
DoCmd.RunSQL "DELETE [TempTable2].* FROM [TempTable2]" 'clear temp2
With Application.FileSearch
.FileName = "*" 'will change to *.xls post-test
.SearchSubFolders = True
.LookIn = "C:Documents and SettingsrhodedfMy Documents"
.Execute
For Each varFoundFile In .FoundFiles
lngFile = lngFile + 1
fDate = FileDateTime(varFoundFile)
mySQL = "INSERT INTO TempTable2 (SourceName, FileDate) SELECT '" & varFoundFile & "', #" & fDate & "#"
DoCmd.RunSQL mySQL 'load all files found into temp2
Next varFoundFile
.NewSearch
End With
Call Increment(20, "Tallying new Files", 800)
mySQL = "SELECT COUNT([TempTable2 Without Matching Temp Table].SourceName) AS CountOfSourceName FROM [TempTable2 Without Matching Temp Table]"
rs.Open mySQL
rs.MoveFirst
If rs.Fields("countofsourcename") > 0 Then
interval = 900 / rs.Fields("countofsourcename")
Call Increment(30, rs.Fields("countofsourcename") & " new files found.", 400)
rs.Close
mySQL = "SELECT * FROM [TempTable2 Without Matching Temp Table]"
rs.Open mySQL
rs.MoveFirst
i = 1
While Not rs.EOF
'entryProp (rs.Fields("sourcename"))
DoCmd.RunSQL "INSERT INTO TempTable (SourceName, FileDate) SELECT '" & rs.Fields("sourcename") & "', #" & rs.Fields("filedate") & "#"
Call Increment(30 + (interval * i), "Importing file #" & i, interval)
rs.MoveNext
i = i + 1
Wend
rs.Close
Else
Call Increment(60, "No new files found.", 1200)
rs.Close
End If
Call Increment(100, "Done", 3200)
mySQL = "SELECT * FROM [ChangesMade]"
rs.Open mySQL
If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF
'editProp(rs.fields("sourcename")
rs.MoveNext
Wend
End If
Set conn = Nothing
End Function

Like I said, everything works. I know this because I have forced the code to stop at all steps and when I do, the form pops up with the current data. But if I run it like a user would, the action all takes place in the background and then the form pops up with 100% and a full bar. Not very useful. I've tried sprinkling DoEvents lines throughout the code and it has no effect.
Am I missing something obvious here? Any help you could provide would be very much appreciated!
Thanks,
Don




In one of my customers databases I have some code which disables the "view" command on a particular form. This is to stop the customer changing from form view to datasheet view whilst entering data which would mess up the data entry if anyone did it.

Since upgrading to MS Access 2007, initially they had no problems, however just lately the front-end completely locks up sometimes as often as once per day, sometimes once a month and various intervals in between.

This is the only place where I manipulate command bars, and that's the only code I can see which has the potential to cause problems.

I have done a quick search of the Internet which didn't suggest anything obvious. Just wondered if anyone else had seen anything similar.




I developed a software using MS Access 2003. I have imported a new progress bar control and used it. It was working properly. After more than two years, I back to this software and tried to run it on another computer. A problem is arise now giving a message "the object doesn't support this property or method" when processing the instruction progressbar1.min=0. This is happening despite that I added the ocx file ccrppr6.ocx into the C:/windows/system32.




I am Oracle programmer but biginner in Ms-Access. My boss ask to develop Employee infor. sys. in Ms-Access database, so I did it but in do so i deselect all the check boxes in startup menu window of default menu bar and menu bar is disappeared. Please, how I can get back the "default menu bar".




When im doing a backup of the tables within the database it's shows a progress bar in the status bar how would i do the same so that a screen comes up with the progress bar in a bigger version ive got my backup database attached

Thanks




So... I'll start off with the facts that I haved queried against MSDN's for Access 2003, forums (including this forum), and some co-workers I work with and have gotten nothing. I am also slightly green to Access programming too.. so, try to be annoyingly clear in your answers if you could...

Here's the scenario, I am trying to create a Progress Bar to visually represent a percentage from a table in access.

I tried out the Microsoft Progress Control 5.0 and 6.0 with no success.. With no events to change the Progress bar I couldnt see a way to make it work.

I moved to using two rectangular boxes, one with a border, one without. The one inside is the filler that would show the progress of the (transparent) box on the outside.. if that makes sense.. But this failed because the Rectangle.Width = ## is not an option. Only Rectangle.Form.Width = ## is an option.. but not the option im looking for...

So... after taking a breather I got back into it and tried filling a Text box with a certain symbol, such as ||||||||||||| or lllllllllllllllllllllll or ******* to show progress through the number of pipes, L's or *'s that were present in the text box after going through an algorithm of sorts... I did this successfully with three *** by using =[Percent]*(111) as the control source...
Which is great! but... alas still has issues.
0% = *
25% = **
50% = **
75% = **
100% = ***
and if you increase the number of stars the text box will fill with 1 star for 0, maxStars -1 for anything > 0 or < 100, and maxStars for 100.

So here comes the question... Does anyone know how to make this Progress bar (or any other mock-progress bar) work on an Access 2003 Report?

Thank you very much in advance... maybe the flat spot on the side of my head will grow back once I stop bangin it on my cubicle wall...




Good Day (or Morning / Evening) to all who is not asleep!

Well - i am not sure if someone asked this question before or if i am the most "detached" VB person in here.
I want to apologize upfront as i am new to VB. My goal is to learn VB to be proficient (and I am already making good progress and written few modules).
Ultimately i would like to use my VB skill to use it for QTP automation.

Also if i posted in the incorrect forum - please let me know.

Basically I have some scripts in VB that i have created in MS Excel and when i copy them and try to use in MS Access module - i get all kind of errors.
I read several posts but cannot find the resolution working for me.
One of the resolution is to enable Tools - References and choose MS Excel Library 10.0 or something like that.
I could not that option - i found few similar selections and still get the same kind of error when Debugging or running the module.

The 2 errors i noticed today:
1) Error 1004 Method 'Range' of object '
2) Procedure or function is not available (or similar error).

Thank you very much!
Alex




I've made progress bars in the past and I've sync'd replicas in the past. But something that's always been a problem for me is meshing the two together.

With Access 2007 is it possible to create a progress bar for use during:


	Code:
	Dim db As DAO.Database

   On Error GoTo CmdSynchronize_Click_Error

Set db = CurrentDb

db.Synchronize (Replica)
DoEvents
db.Close
MsgBox "Synchronization Complete"
Quit
   On Error GoTo 0
   Exit Sub

CmdSynchronize_Click_Error:

    Call ErrorHandler(Err.Number, Err.Description, "CmdSynchronize_Click")
    Exit Sub

I'm unable to find anything already in Access to show what % of completeness exists, or even a way to know what table it is currently checking/working on.
Does a method exist for this or will it involve long hours working on something custom?




A2003

I have a loop on a form, which imports records. During my main loop I display a text status message on the status bar showing how many records have been processed. During this import process I also do INSERT queries and perhaps some UPDATE queries for detail records attached to the main record I am importing. Something is triggering a progress bar to overwrite my text status bar, as the progress bar is in the same location as my status bar text. So in the status bar area I get a lot of flashing between my text I display, and the automatic progress bar.

How do I turn off the progress bar during my loop, then turn it back on at the end of my loop (to be used by other processes)?

Thanks.
(No, Access help was not useful. I tried it.)




Hi all. Haven't had a lot to do with access recently - had a lot to do with it over the years. Client is running 2007 and I need to show a status bar for a large query batch I need to execute. Simple enough however the progress meter now appears bottom right of the screen instead of bottom left like it always was. Some of the messages I wish to show users, combined with the progress bar and its right alignment mean the progress bar does not show - only the text. Is there any way to make a progress meter align bottom left like the good old days when everything was simple. Thanks in advance.. Grapeape




I'm using a progress bar solution posted by Oldsoftboss: http://www.access-programmers.co.uk/...d.php?t=130802. The progress bar should indicate progress on a button click event that initiates various queries.

The problem I have is that the progress bar only runs once all the queries have completed. I have tried inserting the "RunProgressBar" function in different places, but it doesn't have any effect. The function seems to be attached and dependent on the Form_Timer event only.

This is the code for my button click event which sits within my main form:


	Code:
	 
Private Sub BtnImport_Click()
 
Call PBFormOpen 'opens the progress bar form
Call InfoStatusDel 'updates a text box on the progress bar form
Call DeleteOldRecords 'runs a delete query
Call InfoStatusImp 'updates a text box on the progress bar form
Call ImportSpreadsheets 'runs saved imports
Call InfoStatusApp 'updates a text box on the progress bar form
Call AppendArchive 'runs an append query

End Sub

I haven't made any changes (except for form name) to the code in the sample database.

My desired result is as follows:Button click > progress bar form opens Progress bar runs to full while delete query runs (progress bar form stays open) Progress bar runs to full while import runs (again, progress bar form stays open) Progress bar runs to full while append query runs (progress bar form closes) Basically, I want the progress bar to restart 3 times and the progress bar form to close after the last run.

Advice appreciated as always!




I'm doing an export program in VB to export records into Access. I want to add a progress bar as there could be a large number of records. Here's what I've got currently:

pbrExport.Min = 0
pbrExport.Max = mrecData.RecordCount
intCounter = 0

'Do... loop to Add some records to text file

intCounter = intCounter + 1
pbrExport.Value = intCounter
mrecData.MoveNext
Loop Until mrecData.EOF

The problem seems to be that the recordCount is storing -1 as it's value. I've had this problem before, but solved it using OpenKeyset.

However, this time i'm using ADO and don't know how to sort out the problem.

#If ccSQL Then
Set mrecData = mADOConnection.Execute _
("SELECT * FROM [AttendantCircumstances")
#Else
Set mrecData = mADOConnection.Execute _
("SELECT * FROM [Attendant Circumstances]")
#End If

Does anyone have any ideas or is there a better way of doing the progress bar?

Rich




Hi All,

Hopefully someone might be able to help me here, I've searched the forum and haven't found anything covering the creation of a progress bar.

I have a module that is actioned when a macro is triggered via a button on a form.

The problem I have is that when ever the module runs, apart from the view at the bottom of the screen, there is nothing to show clearly that the program is running.

What I would like to be able to do is create a progress bar that sits centrally on screen showing clearly to the end user that something is happening.

Below is the code in my module, which carries out a number of actions from importing data to triggering queries to printing reports etc.

Any assistance on how to include a progress bar within this code would be most appreciated.

=================
START CODE
=================

Option Compare Database

'------------------------------------------------------------
' mcrDDMandateImport
'
'------------------------------------------------------------
Function mcrDDMandateImport()
On Error Resume Next

DoCmd.Echo False, "Running ADD.LP Program"
DoCmd.Hourglass True 'Turn on the Hour Glass
DoCmd.SetWarnings False 'Turn Off the Warnings

'Sets the FS variable to the CreateObject
Set FS = CreateObject("Scripting.FileSystemObject")

'Checks to see if the REJ.txt file exists if it does't then
'the process ends, if it does then it runs the 1st phase of
'the process
If FS.FileExists("C:JohnLeeBackupInformationADDLPIm port.txt") = False Then
MsgBox "File Does Not Exist"
Else
'Import the data from the text file ADDLPImport.txt.
DoCmd.TransferText acImportFixed, "ADDLPImportSpec", "tblDDMandateImport", "C:JohnLeeBackupInformationADDLPImport.txt", False, ""
End If

'Append DGI data from the tblDDMandateImport table to the tblLetterRunDGI table
DoCmd.OpenQuery "qryAppDDMandateDGI", acNormal, acEdit
'Append DGS data from the tblDDMandateImport Table to the tblLetterRunDGS Table
DoCmd.OpenQuery "qryAppDDMandateDGS", acNormal, acEdit
'Append Sky data from the tblDDMandateImport Table to the tblLetterRunSky table
DoCmd.OpenQuery "qryAppDDMandateSky", acNormal, acEdit
'Append data with No address details from the tblDDMandateImport table to the tblNoAddress table
DoCmd.OpenQuery "qryAppNoAddress", acNormal, acEdit

'Append DGI non DD data from the tblDDMandateImport Table to the tblLetterRunDGI2 table
DoCmd.OpenQuery "qryAppDDMandateDGI2", acNormal, acEdit
'Append DGS non DD data from the tblDDMandateImport Table to the tblLetterRunDGS2 table
DoCmd.OpenQuery "qryAppDDMandateDGS2", acNormal, acEdit
'Append Sky non DD data from the tblDDMandateImport Table to the tblLetterRunSky2 table
DoCmd.OpenQuery "qryAppDDMandateSky2", acNormal, acEdit

'Append the data from the tblDGI2_NoPostCode table to the tbltblDGI2_NoPostCode table
DoCmd.OpenQuery "qryAppDGI2_NoPostCode", acNormal, acEdit
'Append the data from the tblDGI_NoPostCode table to the tblDGI_NoPostCode table
DoCmd.OpenQuery "qryAppDGI_NoPostCode", acNormal, acEdit
'Append the data from the tblDGS2_NoPostCode table to the tblDGS2_NoPostCode table
DoCmd.OpenQuery "qryAppDGS2_NoPostCode", acNormal, acEdit
'Append the data from the tblDGS_NoPostCode table to the tblDGS_NoPostCode table
DoCmd.OpenQuery "qryAppDGS_NoPostCode", acNormal, acEdit
'Append the data from the tblSky2_NoPostCode table to the tblSky2_NoPostCode table
DoCmd.OpenQuery "qryAppSky2_NoPostCode", acNormal, acEdit
'Append the data from the tblSky_NoPostCode table to the tblSky_NoPostCode table
DoCmd.OpenQuery "qryAppSky_NoPostCode", acNormal, acEdit

'Append stats data from tblDDMandateImportDGI table to tblDDMandateStatsDGI
DoCmd.OpenQuery "qryAppDDMandateImportStatsDGI", acNormal, acEdit
'Append stats data from tblDDMandateImportDGS table to tblDDMandateStatsDGS
DoCmd.OpenQuery "qryAppDDMandateImportStatsDGS", acNormal, acEdit
'Append stats data from tblDDMandateImportSky table to tblDDMandateStatsSky
DoCmd.OpenQuery "qryAppDDMandateImportStatsSky", acNormal, acEdit
'Append stats data from tblDDMandateImport table to tblDDMandateStats
DoCmd.OpenQuery "qryAppDDMandateImportStatsYear", acNormal, acEdit

'Append stats data from tblDDMandateImport to tblREJ6StatsDGI table
DoCmd.OpenQuery "qryAppDDMandateImportStatsREJ6DGI", acNormal, acEdit
'Append stats data from tblDDMandateImport to tblREJ6StatsDGS table
DoCmd.OpenQuery "qryAppDDMandateImportStatsREJ6DGS", acNormal, acEdit
'Append stats data from tblDDMandateImport to tblREJ6StatsSky table

DoCmd.OpenQuery "qryAppDDMandateImportStatsREJ6Sky", acNormal, acEdit
'Append the data from the tblDGI2_NoPostCode table to the tblNoPostCodeStats table
DoCmd.OpenQuery "qryAppDGI2_NoPostCodeTotblNoPostCodeStats", acNormal, acEdit
'Append the data from the tblDGI_NoPostCode table to the tblNoPostCodeStats table
DoCmd.OpenQuery "qryAppDGI_NoPostCodeTotblNoPostCodeStats", acNormal, acEdit
'Append the data from the tblDGS2_NoPostCode table to the tblNoPostCodeStats table
DoCmd.OpenQuery "qryAppDGS2_NoPostCodeTotblNoPostCodeStats", acNormal, acEdit
'Append the data from the tblDGS_NoPostCode table to the tblNoPostCodeStats table
DoCmd.OpenQuery "qryAppDGS_NoPostCodeTotblNoPostCodeStats", acNormal, acEdit
'Append the data from the tblSky2_NoPostCode table to the tblNoPostCodeStats table
DoCmd.OpenQuery "qryAppSky2_NoPostCodeTotblNoPostCodeStats", acNormal, acEdit
'Append the data from the tblSky_NoPostCode table to the tblNoPostCodeStats table
DoCmd.OpenQuery "qryAppSky_NoPostCodeTotblNoPostCodeStats", acNormal, acEdit

'Append the data from the tblDGI2_NoPostCode table to the tblLetterRunDGI2 table
DoCmd.OpenQuery "qryAppDGI2_NoPostCodeTotblLetterRunDGI2", acNormal, acEdit
'Append the data from the tblDGI_NoPostCode table to the tblLetterRunDGI table
DoCmd.OpenQuery "qryAppDGI_NoPostCodeTotblLetterRunDGI", acNormal, acEdit
'Append the data from the tblDGS2_NoPostCode table to the tblLetterRunDGS2 table
DoCmd.OpenQuery "qryAppDGS2_NoPostCodeTotblLetterRunDGS2", acNormal, acEdit
'Append the data from the tblDGS_NoPostCode table to the tblLetterRunDGS table
DoCmd.OpenQuery "qryAppDGS_NoPostCodeTotblLetterRunDGS", acNormal, acEdit
'Append the data from the tblSky2_NoPostCode table to the tblLetterRunSky2 table
DoCmd.OpenQuery "qryAppSky2_NoPostCodeTotblLetterRunSky2", acNormal, acEdit
'Append the data from the tblSky_NoPostCode table to the tblLetterRunSky table
DoCmd.OpenQuery "qryAppSky_NoPostCodeTotblLetterRunSky", acNormal, acEdit

'Appends the data from the tblNoAddress table to the tblNoAddressStats table
DoCmd.OpenQuery "qryApptblNoAddressTotblNoAddressStats", acNormal, acEdit

'Appends the data from the tblDGI2_NoPostCode table to the tblNoPostCode table
DoCmd.OpenQuery "qryApptblDGI2_NoPostCodeTotblNoPostCode", acNormal, acEdit
'Appends the data from the tblDGI_NoPostCode table to the tblNoPostCode table
DoCmd.OpenQuery "qryApptblDGI_NoPostCodeTotblNoPostCode", acNormal, acEdit
'Appends the data from the tblDGS2_NoPostCode table to the tblNoPostCode table
DoCmd.OpenQuery "qryApptblDGS2_NoPostCodeTotblNoPostCode", acNormal, acEdit
'Appends the data from the tblDGS_NoPostCode table to the tblNoPostCode table
DoCmd.OpenQuery "qryApptblDGS_NoPostCodeTotblNoPostCode", acNormal, acEdit
'Appends the data from the tblSky2_NoPostCode table to the tblNoPostCode table
DoCmd.OpenQuery "qryApptblSky2_NoPostCodeTotblNoPostCode", acNormal, acEdit
'Appends the data from the tblSky_NoPostCode table to the tblNoPostCode table
DoCmd.OpenQuery "qryApptblSky_NoPostCodeTotblNoPostCode", acNormal, acEdit

'Appends the data from the tblNoPostCode table to the tblNoPostCodeStats table
DoCmd.OpenQuery "qryApptblNoPostCodeTotblNoPostCodeStats", acNormal, acEdit

'Append data with the Letter Code "REJ6" to the tblREJ6DGI table
DoCmd.OpenQuery "qryAppREJ6LetterCodeTotblREJ6DGI", acNormal, acEdit
'Append data with the Letter Code "REJ6" to the tblREJ6DGS table
DoCmd.OpenQuery "qryAppREJ6LetterCodeTotblREJ6DGS", acNormal, acEdit
'Append data with the Letter Code "REJ6" to the tblREJ6Sky table
DoCmd.OpenQuery "qryAppREJ6LetterCodeTotblREJ6Sky", acNormal, acEdit

'Print DGI Seperator Page
DoCmd.OpenReport "rptDGI_Seperator", acNormal, "", ""
'Print the DGI rptDDLetterRun report
DoCmd.OpenReport "rptDDLetterRunDGI", acNormal, "", ""
'Print DGS Seperator Page
DoCmd.OpenReport "rptDGS_Seperator", acNormal, "", ""
'Print the DGS rptDDLetterRun report
DoCmd.OpenReport "rptDDLetterRunDGS", acNormal, "", ""
'Print Sky Seperator Page
DoCmd.OpenReport "rptSky_Seperator", acNormal, "", ""
'Print the Sky rptDDLetterRun report
DoCmd.OpenReport "rptDDLetterRunSky", acNormal, "", ""

'If the tables tblLetterRunDGI2, tblLetterRunDGS2 or tblLetterRunSky2 have records in them then
If DCount("*", "tblLetterRunDGI2") > 0 And DCount("*", "tblLetterRunDGS2") > 0 _
And DCount("*", "tblLetterRunSky2") > 0 Then
'Print rptNoDDMandate_Seperator
DoCmd.OpenReport "rptNoDDMandateS_Seperator"
Else
'If the tables tblLetterRunDGI2 and tblLetterRunDGS2 have records in them then
If DCount("*", "tblLetterRunDGI2") > 0 And DCount("*", "tblLetterRunDGS2") > 0 Then
'Print rptNoDDMandate_Seperator
DoCmd.OpenReport "rptNoDDMandateS_Seperator"
Else
'If the tables tblLetterRunDGI2 and tblLetterRunSky2 have records in them then
If DCount("*", "tblLetterRunDGI2") > 0 And DCount("*", "tblLetterRunSky2") > 0 Then
'Print rptNoDDMandate_Seperator
DoCmd.OpenReport "rptNoDDMandateS_Seperator"
Else
'If the tables tblLetterRunDGS2 and tblLetterRunSky2 have records in them then
If DCount("*", "tblLetterRunDGS2") > 0 And DCount("*", "tblLetterRunSky2") > 0 Then
'Print rptNoDDMandate_Seperator
DoCmd.OpenReport "rptNoDDMandateS_Seperator"
Else
'If the table tblLetterRunDGI2 has records in it then
If DCount("*", "tblLetterRunDGI2") > 0 Then
'Print rptNoDDMandate_Seperator
DoCmd.OpenReport "rptNoDDMandateS_Seperator"
Else
'If the table tblLetterRunDGS2 has records in it then
If DCount("*", "tblLetterRunDGS2") > 0 Then
'Print rptNoDDMandate_Seperator
DoCmd.OpenReport "rptNoDDMandateS_Seperator"
Else
'If the table tblLetterRunSky2 has records in it then
If DCount("*", "tblLetterRunSky2") > 0 Then
'Print rptNoDDMandate_Seperator
DoCmd.OpenReport "rptNoDDMandateS_Seperator"
End If
End If
End If
End If
End If
End If
End If

'If the tblLetterRunDGI table has records in it then
If DCount("*", "tblLetterRunDGI2") > 0 Then
'Print the DGI rptLetterRun Report
DoCmd.OpenReport "rptLetterRunDGI", acNormal, "", ""
End If

'If the tblLetterRunDGS table has records in it then
If DCount("*", "tblLetterRunDGS2") > 0 Then
'Print the DGS rptLetterRun Report
DoCmd.OpenReport "rptLetterRunDGS", acNormal, "", ""
End If

'If the tblLetterRunSky table has records in it then
If DCount("*", "tblLetterRunSky2") > 0 Then
'Print the Sky rptLetterRun Report
DoCmd.OpenReport "rptLetterRunSky", acNormal, "", ""
End If

'Print the rptDailyLetterCount report
DoCmd.OpenReport "rptDailyLetterCount", acNormal, "", ""

'If the tblREJ6DGI table has records in it then
If DCount("*", "tblREJ6DGI") > 0 Then
'Print the rptREJ6ListingDGI
DoCmd.OpenReport "rptREJ6ListingDGI", acNormal, "", ""
End If

'If the tblREJ6DGS table has records in it then
If DCount("*", "tblREJ6DGS") > 0 Then
'Print the rptREJ6ListingDGS
DoCmd.OpenReport "rptREJ6ListingDGS", acNormal, "", ""
End If

'If the tblREJ6Sky table has records in it then
If DCount("*", "tblREJ6Sky") > 0 Then
'Print the rptREJ6ListingSky
DoCmd.OpenReport "rptREJ6ListingSky", acNormal, "", ""
End If

'If the tblNoAddress table has records in it then
If DCount("*", "tblNoAddress") > 0 Then
'Print the rptNoAddress report
DoCmd.OpenReport "rptNoAddress", acNormal, "", ""
End If

'If the tblNoPostCode table has records in it then
If DCount("*", "tblNoPostCode") > 0 Then
'Print the rptNoAddress report
DoCmd.OpenReport "rptNoPostCode", acNormal, "", ""
End If

'Append the data from the tblDDMandateImport table to the tblDDMandateImportHistory table
DoCmd.OpenQuery "qryAppDDMandateImportToDDMandateHistory", acNormal, acEdit

Dim dtmDate 'Declare dtmDate variable
Dim CurrentDay 'Declare CurrentDay variable

CurrentDay = Format(Now(), "dd/mm/yy")

Call LastWorkDay 'Call the LastWorkDay Function

LastWorkDayOfMonth = LastWorkDay 'Assign the value of LastWorkDay to the LastWorkDayOfMonth variable

'If the current date ie equal to the LastWork Day Of The Month then
If CurrentDay = LastWorkDayOfMonth Then
'Print the Montly Letter Type Chart
DoCmd.OpenReport "rptChartMonthlyLetterCount", acNormal, "", ""
End If

'Delete the contents of the tblDDMandateImport table
DoCmd.OpenQuery "qryDelDDMandateImport", acNormal, acEdit
'Delete the contents of the tblLetterRunDGI table
DoCmd.OpenQuery "qryDelLetterRunDGI", acNormal, acEdit
'Delete the contents of the tblLetterRunDGS table
DoCmd.OpenQuery "qryDelLetterRunDGS", acNormal, acEdit
'Delete the contents of the tblLetterRunSky table
DoCmd.OpenQuery "qryDelLetterRunSky", acNormal, acEdit

'Delete the contents of the tblREJ6DGI table
DoCmd.OpenQuery "qryDeltblREJ6DGI", acNormal, acEdit
'Delete the contents of the tblREJ6DGS table
DoCmd.OpenQuery "qryDeltblREJ6DGS", acNormal, acEdit
'Delete the contents of the tblREJ6Sky table
DoCmd.OpenQuery "qryDeltblREJ6Sky", acNormal, acEdit
'Delete the Contents of the tblLetterRunDGI2 table
DoCmd.OpenQuery "qryDelLetterRunDGI2", acNormal, acEdit
'Delete the Contents of the tblLetterRunDGS2 table
DoCmd.OpenQuery "qryDelLetterRunDGS2", acNormal, acEdit
'Delete the Contents of the tblLetterRunSky2 table
DoCmd.OpenQuery "qryDelLetterRunSky2", acNormal, acEdit
'Delete the contents of the tblDGI2_NoPostCode table
DoCmd.OpenQuery "qryDeltblDGI2_NoPostCode", acNormal, acEdit
'Delete the contents of the tblDGI_NoPostCode table
DoCmd.OpenQuery "qryDeltblDGI_NoPostCode", acNormal, acEdit
'Delete the contents of the tblDGS2_NoPostCode table
DoCmd.OpenQuery "qryDeltblDGS2_NoPostCode", acNormal, acEdit
'Delete the contents of the tblDGS_NoPostCode table
DoCmd.OpenQuery "qryDeltblDGS_NoPostCode", acNormal, acEdit
'Delete the contents of the tblSky2_NoPostCode table
DoCmd.OpenQuery "qryDeltblSky2_NoPostCode", acNormal, acEdit
'Delete the contents of the tblSky_NoPostCode table
DoCmd.OpenQuery "qryDeltblSky_NoPostCode", acNormal, acEdit
'Delete the contents of the tblNoAddress table
DoCmd.OpenQuery "qryDeltblNoAddress", acNormal, acEdit
'Delete the contents of the tblNoPostCode table
DoCmd.OpenQuery "qryDeltblNoPostCode", acNormal, acEdit

'Export the tblDDMandateImport table to the E Drive Test Data Folder and replace the existing ADDLPImport.txt file
DoCmd.TransferText acExportFixed, "ADDLPImportSpec", "tblDDMandateImport", "C:JohnLeeBackupInformationADDLPImport.txt", False, ""

'Backup Process - Copy the tables listed below to the backup Database ADDLP BU.mdb
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblCompanyTypes", "tblCompanyTypes", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblDDMadateStats", "tblDDMadateStats", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblDDMandateImportHistory", "tblDDMandateImportHistory", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblDDMandateStatsDGI", "tblDDMandateStatsDGI", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblDDMandateStatsDGS", "tblDDMandateStatsDGS", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblDDMandateStatsSky", "tblDDMandateStatsSky", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblLetterTypeCode", "tblLetterTypeCode", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblREJ6StatsDGI", "tblREJ6StatsDGI", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblREJ6StatsDGS", "tblREJ6StatsDGS", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblREJ6StatsSky", "tblREJ6StatsSky", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblNoAddressStats", "tblNoAddressStats", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblNoNoPostCodeStats", "tblNoPostCodeStats", False
''DoCmd.TransferDatabase acExport, "Microsoft Access", "C:JohnLeeBackupInformationADDLP Design BU.mdb", acTable, "tblUserID", "tblUserID", False

Dim TextString3 'Declares the TextString3 variable

TextString3 = "DIRECT DEBIT LETTER " & vbCr
TextString3 = TestString3 & "AND BACKUP PROCESS COMPLETED"

'Outputs the contents of the TextString3 as an on screen message
MsgBox TextString3, vbInformation, "DIRECT DEBIT LETTER AND BACKUP PROCESS"

DoCmd.Echo True, ""
DoCmd.Hourglass False 'Turn Off the Hour Glass
DoCmd.SetWarnings True 'Turn on the Warnings

End Function

====================
END CODE
====================

Thanks in advance.

John




Hi,

I am trying to migrate the whole Ms-Access2003 database to SQL serever 2005. I am doing this by using MS-Access *.adp project which contains the references of SQL server table and Quries.

When I am modifying the table in SQL server2005 that reflaction appears in *.adp project properly but when I am doing the same thing in Ms- Access project i.e. *.adp . It shows warning "The version of Microsoft Office Access doesn't support design change with version of Microsoft SQL Server your Access project is connected to. See Microsoft Office update website for latest information and download(on the help menu, click office on the web). Your design will not be saved.

And also I am not able to save the changes done in access project.
I am also getting another warning like
"You have connected to a version of SQL Server later than SQL Server 2000. The version of Visual Studio or Access that you are using wasd released before the version of SQL server to which you are connected. For this reason, you might encounter problem.

..... some more line.........
.... ... some more line...."

Is there is any way to create /alter the table using Ms-Access Project using *.adp file that changes should be saved in the SQL server.

I have tried this with the Access2007 project also but didn't make any progress.
Waiting for a best possible solution.

Thanks,
Dhruvendra
dhruvendra@hotmail.com




We have an , CMS.mdb, that is published on a Citrix server. The MS Access database has a Startup routine that hides the Database window and opens the MS Access form called frmSwitch.

If I have the CMS.mdb on my C: drive and double click it, it opens to frmSwitch and the Database window is hidden. The button on the task bar on my laptop is titled CMS  [frmSwitch : Form]. If I navigate to another form in the database the button on the task bar has the name of that other form. If I minimize the MS Access window and then maximize it by clicking the button on my task bar, it comes back to the form I was on when I minimized. This is the behavior we would expect.

If instead I use the .ica file to open the application on the Citrix server, I double click the .ica icon on my desktop, and after I log in to Citrix it opens to frmSwitch and the Database window is hidden. This is the behavior we would expect. The button on the task bar on my laptop is titled CMS : Database - Remote.

Issue: If I minimize this .ica window and maximize it by clicking on the button on my task bar, it comes back to the CMS.mdb file but with the database window displayed. This is not the expected behavior.

This is a major issue for us, because once the database window is available (unhidden) to the user, they can change data, forms, reports, queries, etc.

I'm wondering if there is something about the way Citrix or the ICA file is set up that is causing this behavior?




I'm importing several hundred thousand records into a SQL database. I created a classCode Module to display the standard progress bar. When I run the progress bar from test code everything works just fine. The progress bar shows up, the progress bar grows in size, and eventually is removed at the end of the test.

When I use the progress bar class in the actual application the progress bar is initially displayed. But eventually the Access app window goes blank and Access displays "Not Responding" in the title bar. I can't see the progress bar anymore.

The import process takes 1.5 hours.

Everytime I update the progress bar I call RepaintObj

DoCmd.RepaintObject
'Application.Screen.ActiveForm.Repaint
Call SysCmd(acSysCmdUpdateMeter, m_LinesRead)

As you can see I tried calling Repaint on the active form but that didn't work either.

Any suggestions?

Ed