Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 37
  1. #16
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi Orange,



    My mistake, I clicked the link and it looked like there was just a csv file so I didn't download it. I've got it now and it works perfectly.

    Thanks so much for all your help

  2. #17
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi Orange,

    Sorry to be a pain... when I came to close the database and the compact and repair ran I got a "Could not use... RobSmithCopyJ.accdb; file already in use." I've checked task manager and the folder for processes and files and can't see anything. I've also checked the database I uploaded and that compacts ok. Do you know what the issue might be?

    Thanks

  3. #18
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    rob,
    I'm not sure what could/would cause that issue. You said it worked perfectly-- but that it has issue when you try to close the database--is that correct? I suppose it's possible if you were looking at tables or design or if Access was doing something behind the scenes. I'm not aware of anything with the database itself. I did compact it before zipping.
    As I said I did have an issue trying to upload the zip file to this site-- it seemd to be uploading but when finished the file was not attached and there as no message.
    I'll look at my materials, and perhaps sent you another copy if you'd like. I am a little busy this morning.
    Keep me updated on what you find at your end, and if I can be of further assistance.

  4. #19
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi Orange, thanks. I’ve had a look but can’t see anything. I also downloaded the file and get the same when I try to compact as soon as I open it.

    I’ve rebooted, tried to compact when the file is closed and run Microsoft’s repair tool to no avail.

  5. #20
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Rob,

    I had some issues earlier. I tried to download the zip file that I sent you before and had a browser issue (Margeflick browser virus)--could have been related to 4shared -(I use that site about twice a year). Anyway I have removed it (hopefully for good).
    I opened the RobSmithCopyJ.accdb on my machine, and added a couple of close statement for table W and database. I ran it successfully and then closed the MS Access window.

    Again I could not attach a zip to this post???

    I created a new zip which I am attaching here.

    The file name is the same, the ImportSpec should be in the database. You have a copy of the csv test file. You'll have to adjust the routine to show where the csv resides.

    Let me know status.

    1:25PM
    UPDATE: Rob, the zip files I'm trying to attach are fairly large and probably exceed forum limits. The culprit is that your tables --all the non Matches things -- each have 10's of thousands of records. If you can't use this latest zip, I'll try again and delete many of the tables that are not necessary for the Import of csv records into Matches.
    Last edited by orange; 03-03-2020 at 03:36 PM.

  6. #21
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi Orange, thanks for this. I've tried running it. Rather than trying to describe what happens I've done a screen record.

    https://drive.google.com/file/d/1931...ew?usp=sharing

    Thanks

  7. #22
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi Orange, so I've tried to start afresh by creating a blank database and importing table w, the import spec and the module. So far there have been no issues with compacting.

    I renamed the table to "Matches" and changed the file path and table name in the module. When I run this it seems to work but I get this error message:

    Click image for larger version. 

Name:	Untitled.png 
Views:	12 
Size:	6.0 KB 
ID:	41156

    Is this something to do with the fact that the w/Matches table has an extra ID field that is not contained in the csv file?

    I have uploaded the new database and full Matches.csv here:

    https://drive.google.com/file/d/1lEM...ew?usp=sharing

    Thanks

  8. #23
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Rob,

    I watched your video. It seems you are trying to start a macro. This is not a macro. It is a sub procedure. (Damn M$oft has reused some older terminology, just to confuse us all).
    I run the code by clicking the mouse pointer inside the ImportMatches sub code, then click the green triangle in the header. The code executes and writes to the immediate window. The database closes; I reopen it to show the immediate window and the contents of table W. I attached a crude video.
    PS. The forum size limit for file attachment is 2 MB. (That's why this video is so short).

    Let me know status.
    Good luck.
    Attached Files Attached Files

  9. #24
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi Orange, that's what I did after initially trying to run it as a macro. Thanks

  10. #25
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  11. #26
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi orange, no unfortunately not. Did you watch my videos through to the end? The reason I tried to start a new database and import the objects was because of this issue.

    I’d read that the problems may be due to the database being corrupted.

    Thanks

  12. #27
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Rob,
    I'm removing most of your tables and sending a copy of my database specific to the Matches table. It's same as I sent you before except the volume of data in the unnecessary tables (which is creating the large size of the database) is removed. You have all the records for all table in your database.

    You'll have to adjust the location of the csv file in the ImportMatches procedure. Leave the Spec file as it is and see if this works for you. The spec file is stored within the database. I want to ensure it is working before you do any other changes.
    Attached Files Attached Files

  13. #28
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @orange, as a test I downloaded the dB from your Post #27. I changed the path of the CSV file and executed the code.
    Here is the result:
    Code:
    Deleting current contents of table W @ 3/4/2020 2:51:23 PM
    Starting to process the Matches csv file @ 3/4/2020 2:51:23 PM
    Finished importing the csv
    Finished updating the Date on table W
    All done @ 3/4/2020 2:51:24 PM
    You have imported 35  records to W from csv file  @ 3/4/2020 2:51:24 PM

    I did modify your code a little - I added a variable for the path to the CSV file
    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: ImportMatches
    ' Purpose: Routine to empty table W, then import to table W, then convert the Date.
    ' Procedure Kind: Sub
    ' Procedure Access: Public
    ' Author: Jack
    ' Date: 01-Mar-20
    ' ----------------------------------------------------------------
    Sub ImportMatches()
    10  On Error GoTo ImportMatches_Error
    11  Dim sPath As String
    
        '12    sPath = "c:\users\jack\documents\Matches For Orange.csv"  '<<-- orig path
    13  sPath = "D:\Forum\RobSmith\Matches For Orange.csv"
    
    20  Debug.Print "Deleting current contents of table W @ " & Now
    30  CurrentDb.Execute "Delete * from W;", dbFailOnError
    40  DoEvents  'a short break to the OS
    50  Debug.Print "Starting to process the Matches csv file @ " & Now
    60  DoCmd.TransferText acImportDelim, "OrangeImportSpecification", "w", sPath, True
    70  Debug.Print "Finished importing the csv"
    80  DoEvents    'a short break to the OS
    90  CurrentDb.Execute "UPDATE w SET w.ConvDate = CDate(Date)", dbFailOnError
    100 Debug.Print "Finished updating the Date on table W"
    110 Debug.Print "All done @ " & Now
    
    120 On Error GoTo 0
    ImportMatches_Exit:
        DoCmd.Close acTable, "w"
        'DoCmd.CloseDatabase   'commented out the close database
    130 Exit Sub
    
    ImportMatches_Error:
    140 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportMatches, line " & Erl & "."
    150 GoTo ImportMatches_Exit
    End Sub

    No errors importing the CSV file....
    Code executed with no errors.......

  14. #29
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks Steve. It does work for you, too. I have a feeling that Rob is doing something unexpected. But we'll have to work with him to find out what -if that's the issue.

  15. #30
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi Orange, I have downloaded, unzipped and run it without changing anything apart from the file path and name for the full matches csv file.


    It seems to be working except that I get the same Error 94 message (see attached). When I look at the tables, table w has imported all 2,725 lines from the matches files and converted the dates. It has however added an extra ID number for some reason. See attached.


    Looking at the Matches table for some reason it has only imported 2,722 lines.


    I don't mind what the table is called so if the problem is in renaming the table that can be skipped.


    Thanks again for all your help... it's difficult to express how grateful I am!

    Click image for larger version. 

Name:	Untitled.png 
Views:	6 
Size:	6.0 KB 
ID:	41177Click image for larger version. 

Name:	Untitled1.png 
Views:	6 
Size:	35.9 KB 
ID:	41178Click image for larger version. 

Name:	Untitled2.png 
Views:	6 
Size:	34.1 KB 
ID:	41179

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-09-2016, 04:15 PM
  2. Replies: 5
    Last Post: 02-24-2016, 04:32 PM
  3. Replies: 7
    Last Post: 04-18-2014, 05:26 PM
  4. Import from Excel file fails because of date formats
    By wardw in forum Import/Export Data
    Replies: 1
    Last Post: 01-01-2014, 02:20 AM
  5. Replies: 2
    Last Post: 08-17-2010, 01:10 PM

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