Results 1 to 6 of 6
  1. #1
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39

    export creating blank text file

    i have the below code that was working. it would place a datetime stamp in a table. then it would run and export the results of a View to a .txt file. all of a sudden today it is sending zero records and the files are blank. after troubleshooting it seems that it is taking around 10 seconds for the back-end sql table (attached via DSN-Less connection) to update the datetime stamp. so basically the process runs but sees no records per the where clause in the View it is running (the view is looking for records with datetime stamps within certain time frame). not sure if something is going on with the back-end server or not but i had to manually run this process today. being a beginner and inheriting this project i was trying to find out if there is a way to code something to where it ensures the datetime stamps that were just supposedly processed through the UPDATE statement will show up prior to the transfer text process? not sure if there is but it might be better to try something than to tell the end-users to wait twenty seconds and press the button twice, etc. I added a requery but not sure if it is doing anything to anything. it's not failing but it hasn't helped.

    here is the process:

    Private Sub Command0_Click()
    On Error GoTo Err_Command0_Click
    Dim dbCMC1 As DAO.Database
    Dim strSQL1 As String
    Set dbCMC1 = CurrentDb
    strSQL1 = "UPDATE dbo_FieldTicketHeader " & _
    "SET dbo_FieldTicketHeader.ClosedNAVDate = Now() " & _
    "WHERE Not IsNull(dbo_FieldTicketHeader.ClosedTicketDate) AND IsNull(dbo_FieldTicketHeader.ClosedNAVDate) "
    Debug.Print strSQL1
    dbCMC1.Execute strSQL1, dbFailOnError
    DoCmd.Requery
    DoCmd.TransferText acExportDelim, "SWSExport1", "dbo_VW_SWS_OpenBatchBCPDetail", "S:\TicketExport\SWS\ExportFiles\SWSExport1.tx t", False, ""

    Exit_Command0_Click:
    Exit Sub
    Err_Command0_Click:
    MsgBox Err.Description


    Resume Exit_Command0_Click

    End Sub

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    So you think the issue is your UPDATE query? Have you tried pasting the result of strSQL1 into a new blank query and executing it? The other thing I would look at would be the custom saved export procedure SWSExport1. What happens if you run the export wizard and open the SWSExport1 saved export? Do the fields and the data match up?

  3. #3
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    here is what is happening. i click the button and i look at the back-end table and the UPDATE worked placing a datetime stamp in the table. i look at the text file and it is blank. i click the button a second time and the back-end table UPDATE has no affect as it stays at the initial datetime stamp value. i then go to the text file and it isn't blank this time it has the correct data. it was working fine for years until now. do i need to separate the two processes and have two command buttons? i really don't have a clue on what to look for right now.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I suggest you look in the immediate window and copy the resulting SQL into a new query object. What you are describing in post #3 does not add up. So something that you believe happened did not actually happen/occur.

  5. #5
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    i separated this into two processes and command button operations. the first button places the datetime stamp on back-end table through SQL UPDATE and that works fine. the second button executes a SQL VIEW that outputs data and then the MS Access export wizard send a text file to specified location. The sending of the text file works fine. Problem is that it is now blank. Again, in one of my companies this process has worked for over 7 years without a glitch. So i go to SQL Server Management Studio after clicking first command button. I verify the datetime stamp placed correctly. I then try to execute the View in SSMS and no records pull up. I try this several times until after maybe 10 seconds when after executing the records show. So basically when in MS Access front-end clicking the button a second time after going out to a shared drive and verifying text file was blank then going back and clicking on it again was basically executing the View after so many seconds and re-running the export wizard. So this would obviously point to something on the server has changed or something in the View (which i know nothing has been changed there) is affecting the ability for the View to see the datetime stamps on the current set of records and pull them.



    Quote Originally Posted by ItsMe View Post
    I suggest you look in the immediate window and copy the resulting SQL into a new query object. What you are describing in post #3 does not add up. So something that you believe happened did not actually happen/occur.

  6. #6
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    well i have run the update query that i have on my front-end access form that places a datetime in the back-end tables in SSMS. i then run the View that is part of the export wizard and it runs fine with no error. when i run the update query from the front-end access form and then run the View in SSMS on the back-end it pulls zero records until i run it several times and it takes several seconds. not sure what can be happening. when clicking the command button that runs the UPDATE query it places a datetime stamp using Now(). in SSMS when running the UPDATE query i have the function GETDATE(). I see that the format might be a little different as follows:
    Now()
    2016-10-14 12:00:50.000
    GETDATE()
    2016-10-14 12:01:26.283

    i have no idea if this might be something that is affecting it all of a sudden. how do i write the query in MS Access to pull milliseconds like GETDATE().... i mean is there a format for Now()?






    Quote Originally Posted by osupratt View Post
    i separated this into two processes and command button operations. the first button places the datetime stamp on back-end table through SQL UPDATE and that works fine. the second button executes a SQL VIEW that outputs data and then the MS Access export wizard send a text file to specified location. The sending of the text file works fine. Problem is that it is now blank. Again, in one of my companies this process has worked for over 7 years without a glitch. So i go to SQL Server Management Studio after clicking first command button. I verify the datetime stamp placed correctly. I then try to execute the View in SSMS and no records pull up. I try this several times until after maybe 10 seconds when after executing the records show. So basically when in MS Access front-end clicking the button a second time after going out to a shared drive and verifying text file was blank then going back and clicking on it again was basically executing the View after so many seconds and re-running the export wizard. So this would obviously point to something on the server has changed or something in the View (which i know nothing has been changed there) is affecting the ability for the View to see the datetime stamps on the current set of records and pull them.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export from access to multiline text file
    By JoRo50 in forum Import/Export Data
    Replies: 7
    Last Post: 11-04-2014, 06:07 PM
  2. Export Whole table to text file with UTF-8
    By Auto in forum Import/Export Data
    Replies: 3
    Last Post: 08-29-2013, 05:09 PM
  3. Get Export File name from Unbound Text Box
    By FinChase in forum Import/Export Data
    Replies: 1
    Last Post: 01-11-2012, 11:09 AM
  4. Importing text file with blank fields changes to null
    By Egoyret in forum Import/Export Data
    Replies: 9
    Last Post: 11-10-2011, 01:51 PM
  5. trying to export data to a text file
    By itm in forum Access
    Replies: 1
    Last Post: 06-30-2011, 10:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums