Storing Excel data in Access (previously posted in Excel forum)

[swallis]

I download data from a 3rd party, contained in a flat file .dbf format. I need to open each file in the folder, copy the data to a central record set, close the original file and at some point remove multiple instances of any unique records. A unique record consists of a combination of name and date. The following code works (without the delete duplicates) but very quickly runs out of rows in Excel. Which means I need to use Access!! I enjoy playing with Excel and Vba, but have always been daunted by Access. Searching has only confused me more, but I think I’ve worked out how to manually import the first file into a one table database. How do I go about adding to it and removing duplicates (using Vba) and would it be better to remove multiple occurrences as they occur or at the end of importation?

Any help would be appreciated, even if it’s just some clues on search terms. I have Office 2003.

Steve

Sub GetData()
Dim fPATH As String, fNAME As String, NR As Long
Dim wsALL As Worksheet, ws As Worksheet, wbDATA As Workbook
Dim xRow As Long
Dim blnFlag As Boolean
Dim xDirect$, xFname$
Application.ScreenUpdating = False
    ChDir "C:\Price\"
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\Price\"
        .Title = "Choose Folder"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1) & "\"
            xFname$ = Dir(xDirect$, 7)
            Do While xFname$ <> ""
                blnFlag = True
                ActiveCell.Offset(xRow) = xFname$
                xRow = xRow + 1
                xFname$ = Dir
            Loop
        End If
   
        If .SelectedItems.Count <> 0 Then
            fPATH = .SelectedItems(1) & "\"
            fNAME = Dir(fPATH & "*f.dbf")
            Do While Len(fNAME) > 0
            Set wbDATA = Workbooks.Open(fPATH & fNAME)
            Range("A2", Range("ap").End(xlDown)).Select
            Selection.Copy
            Workbooks("Test dbf.xls").Activate
            Sheets("form").Select
            Cells(Rows.Count, "a").End(xlUp).Offset(1).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
              With Selection.Font
                   .Name = "Arial"
                   .Size = 10
              End With
             wbDATA.Close False
            fNAME = Dir
            Loop
        End If
    End With
      
    If blnFlag = False Then MsgBox "No files found"
    
  Application.ScreenUpdating = True
  
End Sub

 

[xenou]

I found this searching under "import dbf msaccess":
database - Open / Import a DBF file into MSAccess 2003 - Stack Overflow

As far as removing duplicates, you could just import to one table (a staging table) then transfer to the "real" table. Duplicates can be removed by having appropriate indexes/primary keys on the real table. Another option would be to use a remove duplicates query after importing (either with or without a staging table).

 

[Joe4]

If you are going to Cross-Post this, please provide the link to the other thread per our rules on Cross-Posting (rule #13 here: Forum Rules).

Here is that link: http://www.mrexcel.com/forum/excel-...ore-data-excel-visual-basic-applications.html

 

[Micron]

Not quite enough info around what you have and need to do for me to suggest a concrete solution. One would be to link to the dbf file instead of importing, then using an append query to append to the table. I'd set the field combination to be a unique composite index and deal with Access system warnings about not being able to append records where the index values already exist. More than one way to do that. This would be a mostly manual method.

But you might have a different dbf file name each time. So you could create an import procedure that appends the path to a table (or manually import or link the file) and create your own table list of imported/linked files and allow the user to choose one from a form listbox. You might be able to restrict the list process to files whose names end in dbf. You'd pass the name/path of the file to a procedure that runs your action query against the main table. In that code, you'd ignore the warnings, either by temporarily turning them off or less dangerously, code to Resume Next on that error).

Then again, the missing details may rule out either of those two ideas.

%$#@&&! I HATE it when I respond to a cross posted problem and it isn't declared.

 

[swallis]

If you are going to Cross-Post this, please provide the link to the other thread per our rules on Cross-Posting (rule #13 here: Forum Rules).

Here is that link: http://www.mrexcel.com/forum/excel-...ore-data-excel-visual-basic-applications.html

My apologies. For some reason I've always considered Cross Posting to be the same question on 2 different resource web sites. Will remember in future.

steve

 

[swallis]

Not quite enough info around what you have and need to do for me to suggest a concrete solution.

Thanks for the reply micron and again, apologies for the inappropriate cross post. What I'm trying to do is run a macro from an Excel spreadsheet which loops through dozens of different .dbf files, extract all the information from them and consolidate it in one place. There will be many repetitions of the same unique data rows contains in the various .dbf files and these need to be deleted. The only user input is to be selecting the folder which contains the relevant .dbf files. I can do all that with vba in Excel, but am struggling with Access. In the interests of full disclosure and not "wasting" anybody's time, my hobby is playing with excel vba and I use horse racing as a means to indulge that hobby. Hobbies cost money and being retired I'm trying to reduce costs. The object of this exercise is to allow me to purchase one data download, instead of two, as I presently do. Specifically each download contains details of the last 10 Starts. Over a period of time I can build a full history, but each time I download it's going to contain at least some repetitions.

I'd appreciate any help.

Steve

 

[Joe4]

My apologies. For some reason I've always considered Cross Posting to be the same question on 2 different resource web sites. Will remember in future.

It causes the same issues as explained in the "So What's the Bid Deal" here of this link: Excelguru Help Site - A message to forum cross posters, whether or not they are both posted at the same web site, or a different one.

Or if at the same site, some people may consider it to be a duplicate post (which is addressed in Rule #12).

Basically, we will allow you to do it, as long as you mention that you are doing so in each one and provide links to the other. That way, no one is wasting their time on a question which may have been answered in the other thread.

 

[Micron]

Here's a point of view from someone who likes to help out as long as it's not a waste of time. I only wish to devote time where solutions are not simultaneously being proposed elsewhere. If an issue is cross posted and declared, I'll take a peek there to see how it's going, but likely won't chime in unless I think I have a simple solution. To do otherwise means either having to monitor two separate threads looking for progress, or take the chance I come up with something before someone else does elsewhere. Sometimes that takes work and a substantial amount of time, so I usually opt not to take a chance on it. Speaking of time, I spent about 20 minutes on this reply, only to almost lose it as the site seemed to go down in the middle of it. Fortunately, I was able to save the text to notepad.

As you can see beside my user name, I have almost 800 posts in less than a year, probably 99.75% give and .25% take, so I don't feel bad about not participating in cross posted issues. IMHO, it would be nice if people stopped using the shotgun approach and posted in one place, giving it time to see what happens before posting elsewhere. Too bad that this site doesn't provide a means of marking a thread as solved (AFAIK) so that you could close the other one. After all, you seek an Access solution, so I think the post should be in the Access forum. Enough lecturing.

There will be many repetitions of the same unique data rows
I ignored this assertion in the beginning, but really, it is an oxymoron when you think about it. In database terms at least, nothing can be unique and still be repeated.
If you in fact want a macro, I can't help because save for the sole exception of AutoExec macros, I never use them and don't want to learn. I only use vba when it comes to effecting actions in a db. Whichever method, it sounds like what you want in an Access database is a means of using the CommonFilePicker or CommonFolderPicker dialog to navigate to a folder, select your dbf file and either import it or just import the data from it into a table. I have done this sort of thing with spreadsheets and text files but not dbf files. Either way, the goal would be to get the file data into a table. The duplication would be avoided by creating a composite index if there was a combination of fields that made a record unique, or by creating a single unique index if not, and letting code suppress the error messages about not appending duplicate values. Per post #4, there is not enough info around what you have and what you want to do to be able to say much else at this point. I also wonder if you need more background in creating a database so that you avoid the all too common pitfalls around naming objects and designing tables and relationships.

 

[swallis]

Ok. I guess I'll have to keep searching. I can't believe that nobody's asked a similar question before. Thanks Micron for at least taking the time to think about it.

Just a little bit of self justification here, I've never cross posted previously, this time I waited nearly a day after posting in the Excel forum without a response. Assuming that thread was dead I tried Access and Referred to the Excel post when I did. I accept that technically I transgressed, but I certainly wasn't trying to obtain any sort of unfair advantage.

 

[Micron]

Sorry if it sounds like I was coming down too hard on you. Didn't mean to come across that way.

I waited nearly a day after posting in the Excel forum without a response.

The page you were directed to about cross posting was spot on in saying you need to wait a day or two. One thing you can do after that period of time is ensure your post stays near the top by replying to it yourself (like, "No clues, anybody?").

I can't believe that nobody's asked a similar question before.

I would say it has been asked and answered many times in many forums. When not done via linking files like spreadsheets, text or csv, or parsing delimited values, it's usually done via automation. Don't overlook the fact that all I'm saying is it's too complicated for macros, which is what you said you were trying to create, albeit in Excel. That is confusing because you said you want to replicate the task in Access. I've provided the basics of what is needed; it's just that you haven't provided enough information for anyone to do much more than that (IMHO).