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 RepliesDear Forum,
please let me ask a second question. I have two tables tbX and tbY, which both contain a column called "LastName". What I am looking for is a query or macro, which compares the columns "LastName" of tbX and tbY and then tells me, which items are contained in tbX but not in tbY.
To make it more complicated I have two cases. In the first case both tables are located in the same database. However, in the second case one of the two tables is not part of my database but located in a different Access-file in a different directory.
I would be happy for any suggestions.
Thank you.
Diana
We have a lookup table that has a list of CLIN numbers and their costs. The contract that governs those CLIN numbers and costs will be changing to entirely new numbers. Unfortunately, I still need to have the old and new CLIN numbers linked to the other tables.
Will I need to merge all the CLIN numbers into one lookup table, or can I do it from two lookup tables?
The SQL statement
SELECT pendingdetailsDrum.ID, DateValue([orderDate]) AS DatePortion
FROM pendingdetailsDrum
WHERE DateValue([orderDate])=DateValue('1/2/2006')
is giving me Internal OLE Automation Error, however when I remove the Where clause it works.
Can someone explain why? OR...
The reason I am using DateValue is because the orderDate field has a date portion and a time portion. When I put a date in the criteria it is not found because of the time portion. I guess I could run an update query and change all the date + time to just date, however , I would rather use the SQL Statement using the DateValue function.
Thanks so much for your responses.
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
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??
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??
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
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
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
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 RelatedHi 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
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
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
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
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?
I am looking to design and create a user interface for my database. Preferably hiding the standard access interface and upon opening the database have a pop up form with buttons to direct users to forms queries.
View 3 Replies View RelatedI have a database sitting on my server for which individual users login to over the network with their own User name and password.
Within my database there are links to data from external sources (SQL based).
My query is, if the database remains open at all times, once the link to the data has been established after keying the SQL database password, how do I stop it from asking each user that logs in to enter the password again each time they dip into the database? (If Possible).
(Access 97 by the way)
Thanks :confused:
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?
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.
Hi,
I want to create a delete query that uses information stored in an external excel file. This file is constantly being updated so it isn't ideal to keep loading it into the access database. I need to use the excel file to remove any records appearing in my access table that are matching with the excel file. Make sense? Can anyone out there help - it this at all possible? Thanks,
I have an Access db in a 3 person multi-user environment on a Windows network.
There is a "PRODUCTION" db and a "DEVELOPMENT" db.
Let's call them PROD and DEV.
PROD has the most current data, shipping records, item master, customer data, sales, etc. - but not the most current structure.
DEV has the most current struture - all the front end stuff - forms, functions, modules, etc. - but not the most current data.
The way I've handled this in the past (it seemed to work) was to take the PROD db and rename it to PRODX. Then take the DEV db and rename it PROD. Then open DEV (now called PROD) using the usual shortcut to PROD.
So now that opens fine, and I have to update all the tables from PRODX - and I mean ALL of them - since I don't really know what data has changed since the last update.If I try to delete records I get blocked by access because of all the related records (I don't have cascade delete set on every relationship). So I delete the whole table - ALL of them (this requires me to also delete the relationships). Then I IMPORT all the tables from PRODX (these have the current data). But now the relationships are all gone. It seemed at first that the relationships were back and intact - but when I last looked they were gone. So this is my problem.
HOW do I COMPLETELY empty ALL of the tables - or even delete them - and then restore them or repopulate them from an exact copy of the db but with current table data - and WITHOUT affecting the relationships?
Obviously any back end structural changes have to be handled differently. Usually by manually making the same change on the PROD db that I had in the DEV db - because deleting the table will cause the structural changes to be lost.
One last thing - I've been working this way on an un-split db, and now I'm in the process of splitting it - which SHOULD make updates much easier.
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]....
I have a subroutine that works well for sending emails. A need has arisen to, in some cases, send an Access report as a PDF attachment. I know how to do this using the SendObject method, but that won't work in this app. I also don't want to have to first save the report as a pdf file. I think I'm close, but I don't know the syntax for passing the report name as the Source parameter in the Attachments.Add method.
Code:
If strEmailAddress = "jsmith" Then
.Attachments.Add source:=MyReportName, type:=acFormatPDF
End If
On Error GoTo SendErr
.Send
On Error GoTo 0
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 RelatedI am attempting to use some external data to populate fields in my DB. I would like to reformat the ProductID in my DB to match a ProductName coming into my DB. We have many products that have 2 pieces. If the product does have two pieces, the external database has two ProductNames that look like this:
0000967
2000967
I would like my database to be able to pull information for each of these 2 part products (they will be displayed as one product in our DB, never to be seperated). I have a form that gives the exact measurements of the first piece by using the ProductName and matching my ProductID (0000967). I would now like to write a little VBA to populate some fields on the forms that are pulled from 2000967.
something like:
Forms!Product!txtField2 = DLookup("[FieldName]", "TableName", "[ProductName] = Forms!Product!ProductID")
The issue is that I need to only get the trailing 6 digits of my ProductID and add a 2 at the beginning. Is there a quick Format syntax I could use to accomplish this?