Lol, no, so I will not write it

[Tony9703]

My company currently has a large amount of data in Excel. We think we would be better suited to have it in Access. However, we need to be able to import new data on a monthly basis, while simultaneously maintaining the old data in a monthly and YTD format. From time to time, new types and new categories of data present themselves, which is something that we need to be able to handle, as well. Lastly, this process should be as automated as possible. We should not have to manually import vast quantities of data, nor should we have to copy/paste the same data into different sections.

worth it/not worth it?

 

[SkipVought]

Hi,

"worth it/not worth it?"

Depends.

Skip,

_[red][/red]

 

[Tony9703]

Awesome.

How would I go about doing it? Is there a way to import data from MS excel into a row in access? I'm having trouble importing "Into an existing table

 

[SkipVought]

I've used this to append rows...

Sub AppendData2Temp()
    Dim oFSO, oFLD, oFIL, sSQL As String, x
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFLD = oFSO.GetFolder("M:\FP_REPORTS\BUILD_PLAN_INFO\Source Workbooks")
    
    For Each oFIL In oFLD.files
        
        
        If Not UCase(oFIL.Name) Like "*CONDENSED*" Then
        
            DoCmd.TransferSpreadsheet _
                acImport, _
                acSpreadsheetTypeExcel9, _
                "temp", _
                oFIL.Path, _
                True
        End If
    Next
End Sub

Skip,

_[red][/red]

 

[MichaelRed]

in the universe of data input processing, I would not advise adding new data directly to existing data/structures:

The new/additional data needs a thorough V&V process, This is particularly important in the scenario you mention

" ... new types and new categories of data present themselves ... "

, which you need to see/understand and account for.

Data sources -particularly ones which are NOT from a regular data source (e.g. database) are nortioursly irregular. Individuals enter 'whatever' they think should be there ... or whatever they please ... or just plain whatever, all without regard to data types, field lengths ...

You may (e.g. WILL) also sooner or later run into an Excel Workbook thrown into the mix which has either extra or missing sheets, columns or other anamolies which do not 'fit' the model.

Any and/or all of the above will surely corrupt a database which is not 'expecting' the input. HOW that corruption occurs (and perhaps the degree of corruption) could be an event triggered (and therefore unanticipated) abrupt change in carrear path.

In the circumstances, I would suggest importing the individual sheets of all workbooks, incividually, and executing a lot of V&V processes to assure that the overall structure of the new data is compatabile with existing structures. And -where it is not- presenting the differences to you for consideration.

When the source information changes, I know of no way to "automatically' re-engineer the destination data structure(s) without at least exposing the risk of changing to an undesireable situation. But examining the input can show differences on which you can either make decisions or ask questions to clarify the changes / differences.

Even when the new information conforms to general expectations, each field of each record should be validate for correspondence to data type and length. In this part, you should flag records which do not conform, and return them to the source with notation to correct the information.

I know this sounds tedious (actually it IS tedious), but it follows from some experience. The motto was (and may still be) "There is never time to do it right, but there is ALWAYS time to do it over". But I have at least tried to move on ...




MichaelRed