Importing to Back End DB

[akshatagarwal93]

Hi,



I am quite new to databases in general but am reasonably proficient at using VBA in Access especially.

I have been working on a DB prototype in which a user will collect some data into a text file and import it into the database on a daily basis. Currently, he clicks a button (Data Upload), he is then prompted to select the text files he wants to upload and then the data is imported into Access.
The next morning, he will have to call the data back, export it into a text file and carry on working on the text file... and the process repeats.

Currently I do this import by running a saved import; The user selects the file he wants and so I can get the filepath. I use this filepath to change the XML code of the saved import (see code below) and the import works fine. The saved import basically appends the data onto a pre-specified table called FAT Joint Table.

CurrentProject.ImportExportSpecifications(0).XML

Now I want to develop the prototype further such that I have a front and back end split appropriately with data in the back and everything else in the front. I have been pondering though on how to import (and export) the data directly to (and from) the back end. I have been able to do this only by running a saved import in the back end from the front end. I do something similar to above in order to do this.

    Dim BackEndPath As String
    Dim apAccess As New Access.Application    
    BackEndPath = Mid(CurrentDb.TableDefs("TurretDetails").Connect, 11)
    Debug.Print BackEndPath
    apAccess.OpenCurrentDatabase BackEndPath, True
    apAccess.Run "TestProc1"

I wanted to find out if this is actually the best and fastest way to do this. I may upgrade to SQL Server if I can convince my boss so I'd want the code to be something similar to what I could do with SQL as the backend.
I've not been able to find anything through Google Search etc so wanted to find out if what I am doing is the best method or whether there is an easier way. I've read some stuff about append queries and making temporary tables, link them then delete them but this seemed to be the easiest so I thought I'd try it first...

Any help would be much appreciated!

Thanks

AA93

[June7]

Set table links in the FE to BE tables. Import and export with linked tables should work.

Recommend UNC pathing for table links.

Review http://forums.aspfree.com/microsoft-...ue-323364.html