Access/Excel External Data Question

Oct 30, 2006

I have a database that contains a series of tables & queries that feed a formatted Excel sheet(s). The problem is it is not very portable.

It works fine on my local computer, but if I give the database to someone else and when the open the Excel template and try to refresh the data from the database they get an error "could not find file C:documents and settingsusername...

If I make a file for them off of documents and settings with my username and put the database in it, it works fine.

So I guess the question is, how do I change the path in Excel to reference the users computer without re-doing all the external queries?

View Replies


ADVERTISEMENT

Problem Entering External Data From Excel Into Access Field Using Input Mask

Mar 5, 2008

Hi,

I'm new to this. I'm trying to enter data (it's actually Latitude and Longitude co-ordinates) from an existing Excel source into an Access database which has input masks of 00°00'00.00"L;0;0 (Latitude) and 000°00'00.00"L;0;0 (Longitude) in the respective fields. However I cannot get the information to import or display correctly. I did an "export data" of the respective table (hence fields) to Excel to try and get the correct entry format. An example of the Lat exported was 24°49'41.81"N and Long was 067°01'44.02"E (but with a very small ' in front but only visible in the data entry line in Excel, not in the actual spreadsheet table???)

However when I try to enter the data (even using the exact same little degree symbol, apostrophe, and quotation marks) it does not enter the access fields correctly. On closer scrutiny of the exported Excel format I note a small ' at the very beginning of the 24°49'41.81"N or 067°01'44.02"E string. But as I said previously only visible in the data entry line next to the formula button. Not on the spreadsheet cell.

However even when I "Paste Special" "values only" my new co-ordinates into the same entry location as one exported, it will still not import, or display correctly. If I go into the Access database directly there is a form where if I need to enter the new co-ordinates (using lat example above) I only have to enter 24 49 41 81 N (spaces between) and it will show correctly as 24°49'41.81"N

I'm getting desparate as I don't want to have to change all the details manually. Anyone know what my correct format from an Excel spreadsheet should be?

Apologies for lengthy story! Difficult to describe problem with degree symbols etc

View 2 Replies View Related

Difficulty Getting External Data In Access 2007

Feb 2, 2007

Hi There,

I have recently upgraded from Office XP to Office 2007. I had an Access database which worked fine in Access XP. In it I had a table linked to an Excel spreadsheet. When I entered data on an access form, it updated the Excel spreadsheet [source document] accordingly. This does not seem to work in Access 2007. The linked table option now does not permit data entry or amendement. The other "Get external data" options create a table in Access which also does not update my source Excel document. The idea is to enter data in Access using a form. This data is placed in an excel spreadsheet. Excel gets some lookup values, and then enters these in a Word mail merge. Without the functionality of a linked table and data entry, I cannot produce new reports.

Can anybody help please?

Regards

Mark M.

View 2 Replies View Related

Exporting Data To External Access Database?

Jan 23, 2015

code that will allow me to take data [All Fields] from a Table in my Database and Insert them into another identically structured Table in another MS Access accdb Database.I would also like to filter for records older than a month [MyDateField]

I have tried the following code I found on line. It doesn't throw an error . . . but it doesn't perform the Insert either.

Code:
Dim ws As DAO.Workspace 'Current workspace (for transaction).
Dim db As DAO.Database 'Inside the transaction.
Dim bInTrans As Boolean 'Flag that transaction is active.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.

[code]....

View 1 Replies View Related

Trouble Importing External Data - Access Limit?

Oct 23, 2006

I'm trying to import about 18 excel spreadsheets into one database in Access. I've been using "get external data" to import the spreadsheets as tables, and the first 8 of them worked fine, but now I cannot import any more excel files. The only error message I'm getting is "An error occurred trying to import file 'C:....xls' The file was not imported." And this error pops up after I have gone through all of the importing steps. Did I exhaust Access's resources? It's not a format issue, and I've restarted, etc. I'm at a loss.

View 2 Replies View Related

Tables :: Access External Data Results In TMP Files

Oct 7, 2013

Running Office 365 (Access 2013). Recent problem occurrence. When I do an 'External Data' command, either from another Access database or an Excel file, the import completes, but I don't see the new table immediately in the navigator section. If I import from another Access database, the new imported tables appear as '~TMPCLP....' tables. If it's from an Excel workbook, the new table doesn't appear at all. It's only after I exit the database and reopen it do I see the new tables correctly named.

View 1 Replies View Related

Modules & VBA :: Get Filtered / Grouped External Data Into MS Access Table

Oct 20, 2014

I have an Excel file with a name range "DBIAS" which identifies all database data.

Then I have an Access file with a form to import that database (better, that named range) into an Access table. While importing, I have to filter some records or grouping by some field.

I cannot run correctly a VBA code to get data (filtered and/or grouped) from that name range and save those records to an existing or a brand new table.

I could get those data as DAO.recordset and printed out with "Debug.print" on immediate window, but I cannot complete the final step: writing those records to a table.

View 3 Replies View Related

Hide External Data And Database Tools Tabs From Access

Jan 17, 2015

I want to hide external data and databasetools tabs only from my access program. How i do it ?

View 3 Replies View Related

General :: Hide External Data And Database Tools Tabs From Access?

Jan 17, 2015

i want to hide external data and databasetools tabs from my access program.

how i do it ?

View 3 Replies View Related

General :: Access Data Export Into Excel As Data Linked To Excel

Oct 21, 2012

how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.Like we have some codes for linking excel file to database mentioned below;

DoCmd.TransferSpreadsheet acLink, , "region", "F:DB PracticeBook1.xlsx", False, "region"

Can we have something like this to link database table in excel file automatically.So that the excel size won't be that big and also it saves processing time.

View 5 Replies View Related

General :: Import Excel Spreadsheet From External Source

Aug 19, 2013

I'm in the process of importing an excel spread sheet from an external source (SharePoint).

I have the link for the file and have edited it from the Https:// to projectspace. intranet.sharpoint etc etc.

The problem I'm having is before importing the data into a new table in my current db I get an error

"The File "projectspace.intranetetc etc" does not exist.

I am using IE8 and the version of Access is 2007.

View 3 Replies View Related

Cant See TXT, XLS When Getting External Data

Jun 7, 2005

I have a program that uses Access 97 as a backbone. When I open Access to get external data my only options are
Microsoft Access
and ODBC Databases.

I have never hade to add or config odbc opjects in the past.

Any help??

Thanks in advance

View 2 Replies View Related

Get External Data

Jun 5, 2006

I have 2 tables both which I need to import data into. I have 2 xls spreadsheets per day and I need to import all the data and build it up over a period of time so that I can use the database to analyse the data. There are around 2000 records per file but I can't seem to import the data unless I select the option to create a new table each time or unless I copy and paste the data. I need to be able to timport one after the other into the same table but I keep getting an error message just saying an error occurred file not imported.

How can I import this data??

View 2 Replies View Related

Get External Data

Jun 5, 2006

I have 2 tables both which I need to import data into. I have 2 xls spreadsheets per day and I need to import all the data and build it up over a period of time so that I can use the database to analyse the data. There are around 2000 records per file but I can't seem to import the data unless I select the option to create a new table each time or unless I copy and paste the data. I need to be able to timport one after the other into the same table but I keep getting an error message just saying an error occurred file not imported.

How can I import this data??

View 1 Replies View Related

Help Help Help???Import External Data???

Sep 12, 2005

Hi
Im having a real problem. Im trying to import a query into Excel so a fancy graph can be made.
In Excel when I go to Data-> Import External Data then connect to my Database. A list of queries and tables appear however the query I want is not in the list but it definately exists in Access why is this??
Really Frustrating

Cheers :confused: :confused: :confused:
bikeboardsurf

View 3 Replies View Related

Link To External Data

Apr 24, 2006

I have a fairly complex database that will have many users. I am looking for a way to sort of link the data in a text box to an external database or text file of some sort to know who created a new record in it.

For example:

The database is on a network drive. Each person who will have access to it has a small file in their C: drive that contains their name. Whenever a new record is created, the name on that file is added to the record to know who is it that added it.

How do I do that?

Thanks

mafhobb

View 1 Replies View Related

How To Insert Data Into External Db

Apr 28, 2005

Hi experts. I want to learn how i can insert data in to external db using vba.
For example , i want insert table names in to the external db.
(A command button in a form )I be happy if some one show me how. Thanks

View 2 Replies View Related

Trouble Getting External Data

Oct 23, 2006

I'm trying to import about 18 excel spreadsheets into one database in Access. I've been using "get external data" to import the spreadsheets as tables, and the first 8 of them worked fine, but now I cannot import any more excel files. The only error message I'm getting is "An error occurred trying to import file 'C:....xls' The file was not imported." And this error pops up after I have gone through all of the importing steps. Did I exhaust Access's resources? It's not a format issue, and I've restarted, etc. I'm at a loss.

View 10 Replies View Related

Data Taken From Access To Excel

Nov 16, 2005

I have a database that works as a sales system. From a table in the database I run a query that calculates the totals for that day.
i.e.

Date……….Dept 1…..Dep2
07/11/05…...£10………£10

What I need is:
1.You click a button
2.It copies the date and finds it in the excel spreadsheet as the sheet will already have a field called date.
3.It will then copy the Dept 1 figure and Dept 2 figure into the spreadsheet where the date matches (in a certain column)

Can this be done?

Kindest Regards

Danian

View 3 Replies View Related

Data From Excel To Access

Jan 5, 2005

Hi I was wondering if there is a way to import the data stored as a excel file to ms access table. For ex:

I have a spreadsheet that has three columns:

Country city population

Each country would have multiple cities.

My Access table "cities: has a similar structure like this. Is there a procedure I can write to copy the data into the Access table from the spreadsheet without having to do it manually?

Please advise.

Thanks in advance

View 1 Replies View Related

Using External Data Wizard In Code

Jun 17, 2005

Hi all

I just to know what the command or code is to generate the External data wizard is ?
I want to import a spreadsheet, the thing this there will be a new spreadsheet everyday with a different name. I guess the easiest way is for the user to select the file themselves. instead of the user going to file, get external data, i thought i could add the code to invoke this wizard on a button.

Thanks in advance

View 1 Replies View Related

Bout Importing N External Data

Mar 12, 2006

as i know, i can import data from excel. but is it possible to make data permanently at excel? i mean Access as a system n excel as data storer..

if possible plz tell me how...

thanks

View 3 Replies View Related

Copying OLE Data To External File

Dec 23, 2006

We have saved image in the database as OLE Object. It is showing “Microsoft Photo Editor 3.0 Photo / Package” in the table. We want to save these pictures as external file in Jpeg format. Please help us to solve this problem.

Thanks in advance

View 1 Replies View Related

Errors Importing External Data

Sep 9, 2005

I have a table that needs to be refreshed with new data monthly. The table has extensive relationship links to other tables. The new data comes to me in a spreadsheet.

I am tying to import the data from the spreadsheet into this existing access table, but errors halt the import. The fields in the spreadsheet are the exact same as in the access table. I am trying to accomplish this task with the Get External Data, Import wizard.

The strange thing is I can import the excel data into a new table without any problems. I then override the old table with the newly created one. The problem with is I have to severe all the relationships before doing so which is a real pain and a cumbersome process

If you can offer any advice, it would really help out

View 10 Replies View Related

Modules & VBA :: Get External Data Dialog

Feb 8, 2014

I am asking my users to interact with the Get External Data dialog, and I'm trying to restrict what they can do.

Code:
DoCmd.RunCommand acCmdImportAttachText

I really like the mapping feature Access uses so I want to use that part of the Wizard, but I don't want my users to import data to the wrong table.Can I skip the first page of the 'Get External Data' Dialog series? Invoke the 'Import Text Wizard' without the previous dialog?see: AWF2. I can get the file path from a File Picker dialog.

Can I set the combobox to a default selection?

View 3 Replies View Related

Switch From Internal To External Data

Oct 20, 2014

I have a front-end/back-end database that another programmer has done some work on for me. He worked on his own copy which wasn't linked to our data. Now I'm ready to integrate his work. What I would like to do is take his improved front-end and link it to the existing back-end tables. All the table names are the same, and none of the fields have changed. Is there an easy way to do this?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved