I created the database in the attachment. I have three tables which are connected. I am trying to create a form to show the connection of the tables. As you can see, I have inserted the 'Item Code' in the form 'Products'. I am trying to add 'Cndl Codes' and the 'Cndl Quantities' of each 'Item Code' showing above 'Item Code' in the form, but I don't know how to do it.
View RepliesHey,
Im doing a login page for my website, and i am having trouble connecting to the DB:
'Database connection info and driver
strCon = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=letmein; DBQ=" & Server.MapPath(strAccessDB)
where strAccessDB = "cs334-45/Kosy/Kosy.mdb"
cs334-45 is my computer name in the virtual directory.
Im getting this error:
Server.MapPath(), ASP 0172 (0x80004005)
The Path parameter for the MapPath method must be a virtual path. A physical path was used.
/Kosy/check_user.asp, line 21
I dont understand. I thought that the virtual directory was mapped to the physical directory
anyway? So why do I have to enter a virtual path?
I have linked tables from SQL Server using ODBC connection that their location never changes. I have used certain fields of those tables to create queries and make table queries to derive to the information I needed.
On these tables on SQL Server, there is new data added daily. Every day, midnight, there's new data records added of whatever transactions took place in that working day. how often do I need to refresh linked tables in this case to get the latest data added. I mean, once I am linked, the make table query using those defined fields, would it get the latest data added by default when the query is executed, or I must refresh linked tables using Linked Table Manager and then run make table query.
Also, if I want the access to automatically refresh linked tables, can I use the following code? I have added this code, and executing it through a button, but I don't see anything happen, the database becomes inactive for couple seconds (I guess while it is updating) but I don't know is it updating the tables for sure or not, though I am not receiving any error when executing the code through the button.
Function RefreshLinkedTables()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.RefreshLink
End If
Next td
End Function
Dear Guys,
How to Establish the connection between Ms-Access2000 & Oracle using DAO Connection through Code?.
I created one table in Oracle and I created User-DSN in ODBC.
I linked that oracle table into Ms-Access MDB using Link table option in ms-Access.
Now, Whenever I open that mdb, we need to establish the ODBC connectivity automatically through code (I mean, we need to refresh automatically).
I need to hardcode all the information in the Ms-Access code.
My User DSN Name: abcd
Oracle Database Name: abcd
Schema Name(User Name): abcd
password: abcd
I am working on an MSACCESS database that reads data from SQL Server through ODBC Connection.
I have multiple tables that I have linked in and created Queries that read the data and created Make Table query.
However, I am asking if there's a way to bring the data through a query that doesn't need the linked tables. Is there a way to bring different fields from different tables into one query without needing to import all the tables, since the tables have many fields that I don't need.
For example in MS Excel, I am able to do that, a query that will bring external data from SQL Server from different tables and only brings the fields that I need for my report.
Our master database has linked tables with ODBC to our SQL database and linked tables to another access database. On the computer i'm running this from a System DSN data source has been created to connect to our SQL database.
The problem I'm having is when I run Macros to kick off Queries, there seems to be some kind of timeout issue and its not consistant. Sometimes in Access after a hour or two, the ODBC links somehow just fails. I have to go up to the menu and go to 'Get External Data' and then 'Link Tables' and relink the ODBC DSN connection. I make sure to hit save password and the end but that doesn't seem to do much. This also happens sometimes when I close and reopen the database.
But it seems erratic, sometimes it can stay up for hours but sometimes just a few mins. Sometimes I can close the Access database and reopen without losing connection. Being so erratic, this makes it very hard for me to run batch files nightly to kick off Macros. Is there some kind of inactivty timer for DSNs? Or maybe with the SQL database itself? Or is the way i'm restablishing the ODBC connection wrong ?
I have MDB database linked to SQL SERVER through VPN connection.I created links to the sql server Links are dsnless..Everything works fine but when I lost VPN connection or sql connection has been broken I can't refresh links to the tables.I receive message 3146 sql connection failed..I must close database and start again...
I tried different methods like ado,dao, and vba docmd.transferdatabase,aclink... but no success, table cant be relinked.
Only way I can relink is to change ip adress in conn.string
E.g. 192.124.0.2 (1st ip- router server ip) and after connection failed i can use 192.124.0.32 (2nd ip - server local ip) and that's it if i lost connection for the 3rd time... i must restart application.
It seems that access database keep the previous connection..how to reset or drop database connection to the sql server and refresh links to the tables with vba code without closing access database...
It seems that there are a few different ways to get info from the tables when developing an applications that i know of:
1)queries
2)dao
3)ado
4)selects without any of the above
I lean towards the last 2 but I need to know this: is there any advantages in using ado as opposed just sql?
For instance i ran this sql:
Private Sub Command0_Click()
Dim SQL As String
Dim strCriteria As String
strCriteria = Forms![form2]![Text1]
SQL = "SELECT * FROM Table1 WHERE (((Table1.clinic)='" & strCriteria & "'))"
DoCmd.OpenForm "frmClinic"
Forms![frmClinic].RecordSource = SQL
End Sub
it worked fine. I could see how a beginner would prefer to use queries over sql but I would prefer to use sql in vba. I've read that dao is older and I should use ado instead. So that leaves ado vs. the way I displayed it in the above code. So which way should I go? If I'm over looking queries or dao in favour of ado/sql, just point out their advantages if you don't mind.
Thanks,
scratch
************** edit: Fixed!!!! *************
The database I'm working on is coming on nicely, thanks to some valuable help from this forum. But I've got a couple of new problems that I just can't seem to get my head around. Really hope someone out there can help!
So...
I have two tables (well, there are more.. but there are two main ones with the important data on them). The first is a list of Customers (you know, the usual Name, contact, telephone etc...). The second is a list of jobs for each customer.
After starting from scratch, I created a nice looking form wth control tabs that on one page shows you the customer information and on the second; the job history for that customer. I then have a third tab which lets you add new jobs. So I'm all chuffed because that is the basics of what I wanted it to do.
However, I need a hard copy of the job report to print out and give to an engineer to fill in or to print in future should a customer wish to see it.
Try as I might, the reports function didn't look as if it was something that could be "designed" the way I wanted it to look. So, I figured another form was in order.
I started by building a query which included all the fields from the two tables mentioned above in it; I.e. So it would pull up a Job Printout by a workorderID number. That way it would show all the company info PLUS the detail of that one particular job.
Problem is this: I can see from the query in table view that the Jobs are listed; but alongside them is a straight listing for all the companies in the database. Basically, the Company who received the job in question is not being shown by the query.... if you follow me. (Apologies if I'm explaining this like a fool).
Question 1 then... is how do I fix this? Is this something to do with these arcane relationships things?
Question 2: How do I create a button to print that one "form" by workorder ID.
Question 3: Did I do the right thing by using a "form" or is there a better way to create a "report" for printing that can be formatted the way I want (with logos and stuff)
Thanks again peeps!
I am trying to get an email address from a table by choosing a employee number from a combo box...it is almost working,
however, it is returning the employee number as opposed to the email address...here is the code I am trying to use:
Private Sub Combo0_AfterUpdate()
Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = myConnection
Dim mySQL As String
mySQL = "SELECT EmailName .*,[Employees] FROM EmployeeNumber"
myRecordSet.Open mySQL, , adOpenStatic, adLockOptimistic
myRecordSet.MoveFirst
myRecordSet.MoveNext
myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing
End Sub
Then, in the section for the command button and the Outlook code, I have:
eMailAddress = Me![Combo0].Value
like I said, it is returning the same value that is in the combo box...if anyone could help, it would be greatly appreciated
I'm a genealogy nut and not so great at computers. I've got a report on cemeteries that gets things like a cemetery number, peoples names and tombstone information from one table. Got that working pretty well. I've got it to print the cemetery number in the detail header (there are 300+ cemeteries each with 1-100s of people in each). I want to take that number and use it in a different table to get travel directions and GPS for each cemetery.
View 3 Replies View RelatedI am very new to access and I am trying to create two tables called Customers and Orders.
The fields in Customers are:
CustomerID (Primary Key), First Name, Last Name, Address.
The fields in Orders are:
Order# (Primary Key), CustomerID, Product Purchased, Address.
There is a One to many relationship between CustomerID in the table Customers, to the CustomerID in Orders.
The problem I am having now is with the Address field.
GOAL: What I am trying to do is have the Orders table automatically get the Address from the Customers Table.
If that doesnt make sense, whenever I create a new Order, I want to enter the customerID, and have it get the Address data from the Customers table automatically put into the proper field in the Orders table.
Hi there,
I just can't figure out how to solve my next problem. What I need to do is to make a query showing all the country's next to germany(nr.5) with their capitals. There are three tables which I can use, nr.1 showing the country_id's with their "neighbour country's", nr.2 showing the name of the country's with their capital_id's and nr.3 showing the name of the capital with their corresponding country-id.
Is there anybody who could help me with this problem, or has some suggestions? If you find this message unclear, i would love to e-mail you the tables.
As i am dutch my english isn't that good.
Thanks in advance
1:
country_1 country_2
12
13
14
15
24
25
26
210
35
45
57
512
612
89
1011
1314
2:
idname capital_id
1Belgium 1
2France 4
3Netherlands 9
4Luxembourg 14
5Germany 6
6Italy 12
7Denmark 15
8United Kingdom 11
9Ireland 16
10Spain 17
11Portugal 18
12Austria 19
13Sweden 20
14Finland 21
15Greece 22
3:
idname peoplecountry_id
1Brussel 1370001
2Antwerpen 4970001
3Gent 2357001
4Parijs 21762432
5Lyon 4184762
6Berlijn 34000005
7Bonn 2930005
8Munchen 12670005
9Amsterdam 10380003
10Rotterdam 5760003
11Londen 66780008
12Rome 28260006
13Milaan 15150006
14Luxemburg 790004
15Kopenhagen 4820007
16Dublin 5260009
17Madrid 320000010
18Lissabon 80700011
19Wenen 151200012
20Stockholm 65100013
21Helsinki 48400014
22Athene 100000015
I am trying to update an existing table in Access with current information that I have imported into Access from excel into a separate table. I was able to use an unmatching query and append all records that were not already in the original table. But there are records in the update table that match the original table, and there in information in fields within that records have new information.I need to find the records that match in the two tables and update the missing data.I am able to create a query that displays the matching records but when I run it as an update query the information in not entered into the original table.
View 7 Replies View RelatedMy father tables contains all the relevant fields with info of my products (which are listed in records/rows). My sister table contains less fields but are all present in the father table. The info is imported from excel daily into the sister table - all the fields here change regularly except for the unique ID which is present in both tables. Because the sister tables' information for the records/rows changes, it is difficult to update the selected fields of the father table.
I want to update selected fields within my father table based on the information I upload from the excel sheet (which becomes the sister table when uploaded)
I have a question about connection strings. Basically I have an access front end and an access back end. It works fine for now, but when it gets rolled out, I don't want to have to manually relink the tables every time. Is it possible to hard-code the database connection strings for all the tables used in the solution as the paths won't change once deployed?
View 4 Replies View RelatedI have two Tables, Contacts and FlowMeters.
On the Flow Meter Form Info is listed specifically for each Flow Meter.
I would like to bring some of the information from the Contacts table on this form.
For Instance if I am on Meter 1T-02 I would like for it to associate that meter number with the name of the customer thats already on the form, then look up that customer's name in the contact table and bring over that customer's contact information.
I built this database over a year ago and it has been a long time since I have worked with the design of it, therefore I am a bit rusty.
Please help if you can.
Thank You
I have now been asked to create 'something' (form, query, whatever) that will:
Allow the engineers to change the date in multiple tables for parts; and,Change (in)activity status information in those tables; and,Delete the same obsolete part(s) from two tables and add that information (one entry) into another table.
I have a question about connection strings. Basically I have an access front end and an access back end. It works fine for now, but when it gets rolled out, I don't want to have to manually relink the tables every time. Is it possible to hard-code the database connection strings for all the tables used in the solution as the paths won't change once deployed?
View 9 Replies View RelatedI am new to Access, so bear with me.
I am using Access 2007.
I have 4 tables created, and they are breaking out information for recording files that are being created from a PBX.
The tables are created as thus:
tbl_1
Key:ext_id | extension
tbl_2
Key:loc_id | location
tbl_3
Key:f-type_id | filetype
tbl_4
Key: mass_id | filename | date | loc_id | ext_id | f-type_id
Now I have created the relationships between the tables for the various ID's. My issue is the following:
I will be automating this process, so when the data is imported to the various tables, I need the relevant keys from tbl_1 tbl_2 and tbl_3 to be added to tbl_4 when the new data is brought into the tables.
As to the process of importing the data, I was looking at using PHP to facilitate this. Any ideas, help or direction would be greatly appreciated. I have looked around the net for an idea of how to solve this, but, being so new, I am not exactly sure how to word the question.
Thanks!
Hi guys.
For my coursework I was told I need complex queries to gian high grades.
This is my problem:
I have many many tables all identicle. They have the same field names just different information on them.
I wanna be able ot query ALL those tables to bring 1 result which I will then make a report out of.
I have tried everything, I fidled with the relationship but can't understand it.
Oh, and the tables have been linked form excel. I know i can't edit it then, but i dont need to.
I would really really apretiate all responses as this needs to be done asap.
Thanks in advanced.
Hi everyone,
I've tried several different things to get around this problem and have found out a lot about what won't work!
Basically I am building a HR database. The main table is M_CandidateDetails and the primary key is CandidateID. This is a foreign key in all of the other tables: MCandidateEducation, MCandidateEquipExp, MCandidateSector. (I've attached a screenshot of the relationships.)
Goal:
What I want to do is create a continuous form with one record for each candidate. A record will show all of the info pertaining to a candidate. The form is to be used to for searching and filtering candidates.
Attempts..
First I tried creating a query to combine all of the information. (I've attached a screenshot of the query). The only problem with that was if joe bloggs had worked in 3 different industry sectors there would be 3 records for him. (I did have problems with the query not being updatable - solved that though by using the Dynaset property of the form)
Next I tried basing the form only on the M_Candidates table and using listboxes to display the information from the many-side of the various tables. But that didn't work as you cannot use an unbound control on a continuous form.
Then I tried putting in subforms instead of listboxes but you can't use subforms on a continuous form.
I'm out of ideas now- can anyone give any tips?
I have 3 tables.
One is a list of fishermen with all their info. I used a Code as the primary key.
The 2nd is a fish ticket sheet with fish tickets entered and the code in there as a foreign key in the relationship between the two.
I create a report listing the fish tickets and prices perfectly for the 4 fishermen I have entered fish tickets for. (I have 140 fishermen in the main table)
I added a 3rd table for payments made to the fishermen. There are two payments for 2 of the fishermen.
then, I go into report design view and drag in two of the cells from that 3rd table into my report.
The problem is the report then prints JUST the fish tickets and payments for the two fishermen that have payments...not the info for all 4 fishermen. I need to print out settlements for all the fishermen whether they have payments or not?
I am importing the updated Employee Roster information from Excel to a table called "Weekly Roster Check" (contains new info). I have a table called "Current Chit Board" that has an Employee Roster that I update every week (contains old info). I want a query to find employees who are promoted and their title has changed. then I want to run an update query to update these results to the Current Chit Board table.
Every method I try, returns either no information or information that is not what I want.
I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.
What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.
So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records.
I have a form that I need to turn into a table. The table will be updated each day with new information but I dont know what to do to keep the previous days info. I have attached the form ....
View 3 Replies View Related