ok I have an access db which is all on access. the dbase has been operational now for 3 years and working well however, do to business needs and changes I'm trying to figure out a better way to collect and share data.
So the backend of the database is stored on our local office server when users log on to enter or review data they must be in the local office. We now had the need to access the database remotely from customer sites. Now the way this works is we use a local dialer to connect to our company's network and then are routed to our local server (you can imagine how slow this is) it is impossible to operate the current database this way. So I have been reading different posts and different options (front page, sql, asp etc.) What
I would like to do is convert my dbase to a program which can allow input and review of data both remotely and locally without a huge speed loss. What is the best approch for me?
I have always had trouble with this and no matter how much I read I cannot get a visual picture of how to do this.So if any of you could show me how this can be done. It would be appreciated more than you can ever know.A company has many employees.Each employee can have many certificates.So how do I set up the tables and the RELATIONSHIPS(this is the part I can't understand), so when I create a form and enter the different types of certificates they all go to the right table?This is what I have so far.Employee TABLEEmployeeID(PK)EmployeeFirstNameEmployeeLastNameEmployessAddressEmployeeCertificateOneEmployeeCertificateTwoEmployeeCertificateThreeEmployeeCertificateFouretc....toEmployeeCertificateEightCertificate TABLECertificateID(PK)CertificateNameCould someone explain in detail how I can do this?I really would like to understand this.I'm sure it is simple but for some reason I just can't get a visual picture in my head on how to do it.I'm a visual learner, I have to see it work and then read how it works to fully understand how something works.Thanks in advance.P.S. I forgot to mention that I need DATE fields for the Employee Certificates because they expire after a certain time.I was not sure where to put those.
I designed a form to provide options for a group of reports. Each option when clicked, runs a specific report. On my machine when I created and ran the form, worked very well. This database is on a shared network drive with few other people having access to it. When these other users opened this form (they don't have problems running ANY other form), they got errors such as "Undefined Format function in the expression". I checked on their machines, the Access version and permissions of the database (I haven't set any special permissions on this database at all). It is same as mine. Please help me. Why is this happening and what should be done to avoid it? The database opens in a default shared mode.
I'm really sorry to bother with my stupid questions, but I'm definitely not a programmer, and after reading about Access, SQL and ASP so much, I feel really confused...
I've already explained what I want to do in the SQL server forum. It's for an experimental project, I need a database that keeps only 100 records, and every new entry of data replaces the oldest record. So in the end there are always only 100 records.
Well, Lauramc nicely gave me this code that should do that:Code:CreateTrigger YourTable_Trigger1On YourTableAFTER INSERTAS DECLARE @OldestID intSET @OldestID = (SELECT MIN(YourTable.ID) FROM YourTable)--Table name is YourTable, with a primary key of ID.IF (SELECT COUNT(YourTable.ID) FROM YourTable) = 101 --You can also use @@RowCount to get the number of rows.BEGIN DELETE FROM YourTable WHERE YourTable.[ID] = @OldestIDEND
But all I know is how to do is put some simple SELECT statements in an ASP page to call a csv database.
I've realised that I might need to use Access but I don't understand anything.
- Should I use Access and if yes, where am I supposed to copy this code? Should I bring any modification to it?
- Apparently it's possible to create a database from SQL, does it mean not using Access? Where should I type the code? In what sort of file?
Please please, can someone tell me it's actually very simple?
I am now right at the last fence in what has been an "interesting" assignment! Sending an attachemnt from Access through winfax!
I have found the following excellent code but, the problem is that the documemnt appears in a "preview" kind of pane, I just want it to go ahead and send! Everything else works fine
Any sugestions pls?
Many Thanks Andy
Public Function SendFax()
'On Error GoTo ErrorHandler
Dim strRecipient As String Dim strFax As String Dim strattach As String
'Test for required fields strFax = Forms!frmFinanceProposal!Text1077 If strFax = "" Then MsgBox "Please enter a fax number" GoTo ErrorHandlerExit End If
'Start DDE connection to WinFax. 'Create the link and disable automatic reception in WinFax lngChannel = DDEInitiate(Application:="FAXMNG32", topic:="CONTROL") DDEExecute ChanNum:=lngChannel, Command:="GoIdle" DDETerminate ChanNum:=lngChannel
'Create a new link with the TRANSMIT topic. lngChannel = DDEInitiate("FAXMNG32", "TRANSMIT")
I created a db, tested it with dummy info and all was fine. I then imported live data from another db. When I came to add new records, discovered that, in the table design, a default value of 0 (zero) has been entered for fields where a look up table is used (where I had used Autonumber for the ID). Solved that easily enough, but later I amended a text box to a combo box and the same thing happened. Why does it do this?
With rstComment .AddNew ![CommentNo] = DCount("CommentNo", Me.OpenArgs, strCriteria) .Fields(strItem) = nNumber ![ProgressionID] = Me![cboProgressionID] ![Comment] = Me![txtComment] ![PersID] = 0 'Will be changed by SQL Server ![Date] = Now() 'Will be changed by SQL Server .Update .Close End With
This code is used in a form that is accessed via a main form when a progression is selected. The form that opens allows the user to selct a type of progression and to enter a comment. This data is then transferred to a table.
This form is used elsewhere in the database and works fine. The code is exactly the same. The form name is different and the table it points to is the same design of table as the other but obviously named differently. i am confident it is referenced correctly. But you lot are the experts.
I am not trained in VBA and have only just started to understand bits of it.
Action: 1) Update the blank fields in a table + add newly imported records but...make sure that there are no duplicates. I need to Append the records from a table called: XLS_Imp_11_27_07 to my main Table " Invoice Tracking for A/P 10_30". The fields found in the XLS _ table: Release Dt, Entry Dt, Liquidation Dt may have been populated by a live report found on Internet. Since it's a live report, when Appended, to the other table, it will create numerous duplicates. The fields found after the 7 first fields in the Invoice Tracking Table may have been updated by users
Is there a way to avoid duplicates and get the data from one table to the other without wiping out whatever is already filled-in? :(
i'm writing an Access database to quote for windows & doors, there are many variants like style, width, height, glass types, security specifications and about 4 other options.
what would be the best way of working out how to add the extras for each option,
The price would be based on the style first, then depending on width and height, then on what options were ticked . i.e securtity spec yes/no, then on what glass type, the price gfor that would be based on the entered width x height etc.
is it possible to do a look up, like you can in Excel where it looks at at grid/matrix based on style number and width x height to get the price, but then how would i get it to add the other options which will depend on size and number of openings.
I am completely new to Access but probably all I will need is a gentle push in the right direction. I'd like to think I'm fairly quick at learning new things. Thank you in advance, here's where I am, (first step anyway):
Table 01 Parts List ID - name - cost
Table 02 Item List ID - nick name - description
Table 03 Items to parts relationship (this is where I fall apart) ID - Item ID (relationship to table 02) - Item nickname - PartID (relationship to Table 01) - Part name - Part quantity.
What I am trying to do: I resell items in table 02 which are built of various parts from table 01. I need to generate a table that says: Item 1 is built from 4 of part 1, 2 of part 2, 9 of part 3, and the total cost of parts for Item 1 is x. I want to update the parts list cost on a regular basis and have the item price change reflected.
If I can understand how to make this work, I can ultimately add in labor and overhead etc etc. Baby steps for now.
I am using Access 2003 in a simple database and want to add digital pictures of each item in my database.
Before trying to enhance the database with pictures; the various fields are filled with either descriptive text or numbers. The size of the mdb database file is 464k.
I recently wanted to add a picture of each database entry. After adding a picture that is only 300k in size the mdb file increase to 18MD. Then after adding another picture also of 300k bytes my database grew to 25MB.
In the drop down menus Under Tools - Database Utilities - I ran the Compact & Repair Database. This did not reduce the size.
In the database Form I am using a Bound Object Frame to hold the picture and yes I am not linking the picture I am including it in the database. But, would only expect it to increase the database size by slightly more than the picture size.
I should also point out that the only way I have found to get the picture to be visible (not just an icon of the type of picture file) is to right click on the Bound Object Frame scroll down and choose Insert Object. Click on Create New and select Adobe Photoshop Image. This open adobe photoshop I make sure the picture properties are creating a picture of 300k.
So my question(s) is again. Why is my database increasing in size so rapidly? Is there a better to insert picture into a database?
I was using access 2000 to build a small office app. It will be on a network and will generally be used by only 2 or 3 people(max). I was wondering if the default jet technology would be good enough for this. If it isn't, what should I use? I looked at other options such as msde and ado but it's a bit confusing when you're new to this stuff.
I'm trying to create a database for a taxi-type service that runs on thursday, friday and saturday nights. My question is: Is there a way to save the database at the end of the night separate from the previous nights, and so that the database is empty for the next night, but reports can be viewed for all of the nights combined?
I unticked all the boxes in the Tools > start up window. I closed my database then reopened it. Now, I can't put these settings back again as I can't see the Tools tab.
I'm by no means an expert when it comes to using access and its many controls that are avaliable to use on forms. Thats why I've come here to seek your help.
I have a database that has been created by someone who has now left the company and it needs a little work done to it.
The ideal thing we would like to get working on form, is that we would like a number of options to be greyed out and only accessable when another option is ticked.
I'm not sure how to group these options together, nor an I sure how make them active only when an specific tick box is ticked. Any help would greatly appreciated on this matter.
:o Please help... I am trying to get the following results. If the Qty is less than 99 bag 10 per, if the qty is between 100 and 999 bag 100 per and if the qty is greater than 999 bag 200 per.
I ned to create a form that asks the user which reports they want to preview/print. I see it as a set of tick boxes with one saying all, then a print button. Im not really sure though, can someone offer some advise or maybe an example?
Hi, How do i create an option inside the form, such that when the user ticks this option, it will print the form in this particular report. If user ticks another option, it will print the form in another particular report format... Thanks...
Is there an option so that turning the mouse wheel will scroll through the screen rather than scrolling through the different records in a form? Thanks! :confused:
I am a bit of a beginner to Access 2003, and I would appreciate any help you can give me? :)
I have a drop down box on a form in Access 2003 with several options when an option is selected from the first drop down I would like a sub catagory of the selected drop down option to appear in the second drop down menu.
For example:
First drop down Hair, Clothes,
If you select Hair it then give the products relating to hair which need to be choosen in the second drop down menu.
I am trying to make a form that will allow me to make several selections and run a report. I want to be able to select from a list of Grade Levels, 9th, 10th, 11th and 12th. After I have picked my grade level or selected more then 1 grade level I want to be able to choose from a drop down list of school district. After I have choosen my grade level and my school district I want to run my report. How do I do that?
I am trying to create a window in access that will have about 30 different boxes that the user of the database can choose from to click on and automatically sort the information in both a Form and Sub-form, and display the information for the User. Frankly this is a little beyond my means, and I need to know where to get started. I have good knowledge of Access but I don't have much experience when it comes to programming in commands and such. Any help would be appreciated.
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.
I have been having issues dealing with "moving servers" addresses, file relocations, and folder name changes.
this makes 'linking' quite difficult.
I came up with this option, and am wondering if this would work.
'Delete old table connection DoCmd.DeleteObject acTable, "ExampleTableLink"
'remake them based on current location. DoCmd.TransferDatabase acLink, "Microsoft Access", Application.CurrentProject.Path & "DatabaseName.mdb", acTable, "ExampleTable", "ExampleTable"
Basically, this code deletes your current link, then recreates it based upon the path of the current database location. As long as the two databases are relativily equal in there locations, one could move the databases / be in completely different locations, and the links would always work fine.
IE:
One computer has the location as w:database folder
Another has q:somewheresomehowIdon'tgetitdatabase folder
3rd example \serverWierdsomeplacehotcoolcheese.blue.orgda tabase folder
and the links would still be established correctly.
In fact, old broken links would be erased and re-established correctly.
I KNOW there are limitations to this.
For example... If this is run from A FRONT END, then the front end's current location is give as the current location.
Can anyone think of a way around this? Without user interaction?
The only option I can think of for this example would be to allow a user to manually run this "relinking" process by re-copying out a new user interface from the backend setup.
IE: If the current front end fails, tell user to go to backend location, open the "backup" user interface, on doing so, the links are re-established and a copy of the front end is put on user Computer with desktop link.
Don't really like that option though... too.. clumsy and dependent on user knowledge / skill.
