473,375 Members | 1,292 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,375 software developers and data experts.

Import to the backend of a split db

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

Oct 9 '06 #1
8 8547
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
Oct 9 '06 #2
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

Oct 9 '06 #3
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.
Oct 9 '06 #4
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

Oct 9 '06 #5
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 ***
Oct 9 '06 #6
"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
Oct 9 '06 #7
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 ***
Oct 9 '06 #8
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

Oct 9 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Derickson | last post by:
I am working with a production runtime Access 2000 db, split front-end and back-end. Running on a network, I need a nightly copy of the backend transferred to a laptop with a temporary network...
4
by: Niranjan | last post by:
I have an Access XP split application operating on Windows XP. I have a form that opens on Startup that opens the backend database and keeps it open until the application is closed. For the first...
3
by: Mark | last post by:
Hi All, Firstly, I am aware from reading previous posts that if someone is determined enough, they will crack an Access Application. However, this problem seems basic but I can't find a...
3
by: DD | last post by:
Hi I have a split database, that is packaged and used in a runtime enviroment. I want to make it easier for the user, a button on the frontend "Export Backend" They can click on the button...
2
by: Peter | last post by:
(I've tried this Q in the ms forums without response. See how it goes here.) Using A2003 but I guess this is not version-specific... If I want to implement row/record level locking on a split...
19
by: Mark 123 | last post by:
I just read at http://groups.google.com/group/microsoft.public.access.forms/browse_frm/thread/766ba7b493eacb63/8a7d5504ee848c21?lnk=st&q=migrate+access+97+to+access+2007&rnum=1#8a7d5504ee848c21...
17
by: ApexData | last post by:
Hello Split DB (FE & BE) Linked. FE compiled to MDE. For security reasons, I have hidden the BackEnd. However, If the network is down or the FE can't find the Backend, then an Access Error...
13
by: rdemyan via AccessMonster.com | last post by:
My front-end code manually links to the backend file. I was wondering what stops Access from linking to the system tables in the backend file. Is it just by virtue that they are hidden? This...
7
by: ShyGuy | last post by:
Is ther a way to copy a table, from the backend of a split database, into a database on another machine from the front end?
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.