Import to the backend of a split db

[FireGeek]

I have a database that is split appropriately. Annually, we need to
add data from .xls files. I have added code so with a click of a
button, it will import this data as a new table into the database. The
problem is that the code is on the front end and thus it imports the
data/new table into the front ent.

How can I accomplish an import (with the click of a button) into the
back end and link it to the front end?

THANKS

FireGeek822

[sara]

Hi -
I do this all the time - my users run a procedure to import .xls files
into several linked tables. The code (below) is in the front end db,
and the tables are in the backend db. This is just the import portion
- I also check to make sure the data doesn't already exist on the table
before the import runs. The command is TransferSpreadsheet. There's
probably Help and Postings on it, too.

Hope this helps.
Sara

strImportFileName = Format([SaleDate], "mm-dd-yy") & " POS12" &
".xls" DoCmd.TransferSpreadsheet acImport, 8,
"T_EE Sales", _
strPath & strImportFileName, True, ""
FireGeek wrote:

I have a database that is split appropriately. Annually, we need to
add data from .xls files. I have added code so with a click of a
button, it will import this data as a new table into the database. The
problem is that the code is on the front end and thus it imports the
data/new table into the front ent.

How can I accomplish an import (with the click of a button) into the
back end and link it to the front end?

THANKS

FireGeek822

[FireGeek]

Sara,

Thanks for the reply. I am already using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tblName,
"C:\Comp.xls", True

Again, the problem is that this imports to the frontend (I want it
imported to the backend) and I need this to link to the frontend after
imported.

FireGeek822

[Newbie]

On 9 Oct 2006 12:26:20 -0700, "FireGeek" <fi*********@hotmail.com>
wrote:

>Sara,

Thanks for the reply. I am already using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tblName,
"C:\Comp.xls", True

Again, the problem is that this imports to the frontend (I want it
imported to the backend) and I need this to link to the frontend after
imported.

FireGeek822

In your front end create a new table but link it to an existing table
in the back end.

If you need help, post back.

[FireGeek]

Newbie -

It seems you don't quite grasp what I am doing here. I don't see how
creating a table in the frontend and linking it to the backend will
help.

I am trying to import an .xls file as a new table into the backend and
then need to link it to the front end.

FireGeek822

[Rich P]

Hi FireGeek,

What you do is to continue importing the sheets directly into the front
end. The trick is to note the new table name then do an Insert Into
query to get the data into the back end. A slightly more sophisticated
technique would be to just import the data directly into the backend
from Excel, but that would require a rewrite of your code. Without
having to rewrite your current code you just copy the data as you have
been. Then in the same command button you add this:

Private Sub Command1_Click()
Import data from excel...
'--get new table name from the import action above - then
DoCmd.RunSql "Insert Into backendTbl select * from " & newTblName
End Sub

The more sophisticated way uses Excel automation - make a reference to
the Excel Object Library from a code module - goto Tools/References and
select Microsoft Excel 11.0 Object Library (or whatever your version of
Excel - if you have more than one version of Excel running on your
machine - pick the highest version). Now you can write code like this:

Sub ImportExcelData()
Dim xl As Excel.Application, wkbk As Excel Workbook, sht As
Excel.WorkSheet, rng As Excel.Range
Dim DB AS DAO.Database, RS As DAO.Recordset
Dim i As Integer, j As Integer

Set xl = CreateObject("Excel.Application")
Set wkbk = xl.Workbooks.Open("C:\dir1\yourWkbk.xls")
Set sht = wkbk.Sheets("Sheet1")
set rng = sht.UsedRange.
Set DB = CurrentDB
Set RS = DB.OpenRecordset("backendTbl")
For i = 1 to rng.Rows.Count
For j = 1 to rng.Columns.Count
RS(i - 1) = rng(i, j)
Next
Next
RS.Close
End Sub

This example uses the Excel Range object to retrieve all the data from
Sheet1 that is contained in the "UsedRange". The "UsedRange" is an
Excel object that consists of all the cells on a sheet which have been
filled with data. Note: when using "UsedRange" if data gets deleted
from rows in the lower part of the sheet, those rows still count as a
used range. So UsedRange only works reliably with New Sheets or sheets
where the data will never change once you have used the UsedRange
object.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Larry Linson]

"FireGeek" <fi*********@hotmail.comwrote

It seems you don't quite grasp what I am
doing here. I don't see how creating a table
in the frontend and linking it to the backend
will help.

A linked table actually exists in the back end, but is referenced by a
TableDef in the front end. Actions performed using the name of the TableDef
in the front end will actually take place on the table in the back end.

I am trying to import an .xls file as a new table
into the backend and then need to link it to
the front end.

Isn't what you _really_ need to do this: you need to move data from the
Excel file into a table in your backend database?

Is there some compelling reason that you cannot create a new (empty) table
in the backend, or in a temporary database (a really good solution for
repeatedly-imported information, about which you can read at MVP Tony Toews'
site, http://www.granite.ab.ca/accsmstr.htm), link it, and then import the
data?

Do you just periodically add a new table to the back end? Do you delete the
old one before you do, or does your database just grow? If the former, then
see Tony's site about using a temporary database. If the latter, aren't you
somehow "storing data in the table name" that should be normalized to save
you work and energy later when you need to use it?

If you can clarify, perhaps someone will be able to offer some more specific
suggestions.

Larry Linson
Microsoft Access MVP

[Rich P]

I left out a few things for the Excel Automation example:

Sub ImportExcelData()
Dim xl As Excel.Application, wkbk As Excel Workbook, sht As
Excel.WorkSheet, rng As Excel.Range
Dim DB AS DAO.Database, RS As DAO.Recordset
Dim i As Integer, j As Integer

Set xl = CreateObject("Excel.Application")
Set wkbk = xl.Workbooks.Open("C:\dir1\yourWkbk.xls")
Set sht = wkbk.Sheets("Sheet1")
set rng = sht.UsedRange.
Set DB = CurrentDB
Set RS = DB.OpenRecordset("backendTbl")
For i = 1 to rng.Rows.Count
RS.AddNew
For j = 1 to rng.Columns.Count
RS(i - 1) = rng(i, j)
Next
RS.Update
Next
RS.Close
xl.Quit
set xl = Nothing
End Sub

You need RS.AddNew and RS.Update to create the new rows in the back end
table, and you also need to quit the xl object or else it remains in
memory and you have to go into the Task manager/Processes tab to look
for your excel application and turn it off from there.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Granny Spitz via AccessMonster.com]

FireGeek wrote:

How can I accomplish an import (with the click of a button) into the
back end and link it to the front end?

I'd just use a make table query to import the Excel spreadsheet into the back
end and then create a link to the new table in the front end like this:

Dim sDBFile As String
Dim sXLSFile As String
Dim sSheet As String
Dim sTableName As String

sDBFile = "T:\IMDB\Backend.mdb"
sXLSFile = "C:\AnnualReports\TotalSales.xls"
sSheet = "Sheet1"
sTableName = "tblTotalSales"

CurrentDb.Execute "SELECT * INTO [;DATABASE=" & _
sDBFile & ";]." & sTableName & _
" FROM [Excel 8.0;HDR=Yes;DATABASE=" & _
sXLSFile & ";].[" & sSheet & "$];", dbFailOnError
DoCmd.TransferDatabase acLink, "Microsoft Access", sDBFile, _
acTable, sTableName, sTableName

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1