Split db Import tbl into the back end

[webisti]

I have a split database and before I split it I was importing tables from
the accounting system. With the split database when I import the tables they
come into the Front End. How do I import them into the Backend. Currently I
have macro that does the import and I have it on the main menu when it comes
up

Thanks for your help

Dim strPath As String
Dim strFile As String
Dim strTable As String
Dim strSpecification As String
Dim intImportType As AcTextTransferType
Dim blnHasFieldNames As Boolean

' Let user select a folder
With Application.FileDialog(4)
If .Show Then
strPath = .SelectedItems(1)
Else
MsgBox "You didn't select a folder", vbExclamation
Exit Sub
End If
End With
If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
DoCmd.OpenForm "frmMessage"
Forms!frmMessage.Repaint
' Loop through the text files in the folder and if saved as decsribed then import them

'
strFile = Dir(strPath & "*.TXT")
Do While strFile <> ""
'IMPORT EBAN
If strFile = "CUST.TXT" Then
strTable = "CUST"


strSpecification = "CUST_ME"
blnHasFieldNames = False
intImportType = acImportDelim
Else
strTable = ""
End If
' Import text file as pre described
If strTable <> "" Then
DoCmd.TransferText _
TransferType:=intImportType, _
SpecificationName:=strSpecification, _
TableName:=strTable, _
FileName:=strPath & strFile, _
HasFieldNames:=blnHasFieldNames
End If

strFile = Dir()

Loop

End Sub

Now this code is in the front end But I need to import these tables into the back end called : auction.accdb
What do I need to do further

Any help much appreciated.

regards

Webisti

[June7]

The TransferText method won't do that. After importing into the frontend, Use SELECT INTO to send the data to the backend. Example:

strPath = "drive:\path\BEfilename.accdb"
CurrentDb.Execute "SELECT * INTO tablename IN '" strPath & "' FROM tablename;"

Alternative is VBA to open the csv and read each line into a table. http://www.bigresource.com/Tracker/Track-vb-zAjbnMPyEv/

[Subwind]

This is possible. I have a database with all tables linked to the frontend, and I import data on a regular basis from text files.

On mine, I first delete all data in the linked tables, then import the new data direct into those now empty tables.

 DoCmd.RunSQL "DELETE * FROM <yourtablename>"
 DoCmd.TransferText acImportDelim, "", "<yourtablename>", "<importfilelocationandname>", False, ""

Does the job I need, would easily be possible to add data on top of old data rather than remove then add all.

I also have a frontend import from Sage Accounts into backend tables.

~Matt

[June7]

Yes, that requires table already exists in backend and frontend has link (or the table and link must be created 'on-the-fly' wtih VBA). Sounds like OP does not have nor want established tables. So, webisti, what is the situation and which option will resolve your issue?

[webisti]

Hi I think I found the solution (with the help of a friend of mine)

Private Sub IMPORT_tbl_Click()
Dim strPath As String
Dim strFile As String
Dim strTable As String
Dim strSpecification As String
Dim intImportType As AcTextTransferType
Dim blnHasFieldNames As Boolean
'DELETE THE SELECTED STRIPES TABLES BEFORE IMPORTING
'DoCmd.DeleteObject acTable, "Cust"
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from cust", False
DoCmd.SetWarnings True
Delete_Table ("cust_tmp")
' Let user select a folder
With Application.FileDialog(4)
If .Show Then
strPath = .SelectedItems(1)
Else
MsgBox "You didn't select a folder", vbExclamation
Exit Sub
End If
End With
If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
'DoCmd.OpenForm "frmMessage"
'Forms!frmMessage.Repaint
' Loop through the text files in the folder and if saved as decsribed then import them

'
strFile = Dir(strPath & "*.TXT")
Do While strFile <> ""
'IMPORT MARD
If strFile = "cust.TXT" Then
strTable = "cust_tmp"
strSpecification = "cust"
blnHasFieldNames = False
intImportType = acLinkDelim

Else
strTable = ""
End If
' Import text file as pre described
If strTable <> "" Then
DoCmd.TransferText _
TransferType:=intImportType, _
SpecificationName:=strSpecification, _
TableName:=strTable, _
FileName:=strPath & strFile, _
HasFieldNames:=blnHasFieldNames
End If

strFile = Dir()

Loop

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO cust SELECT * FROM cust_tmp", False
Delete_Table ("cust_tmp")
DoCmd.SetWarnings True
End Sub

so basically import them in front end and insert them as tables in backend.
easy way I think..still was hoping to find a solution not to do this at all but just to select the path of the db where to import them directly..

[webisti]

of course using a function to delete the tables....