I have an estimating database, the "main" database is on my desktop, then it is also on a Notebook and Laptop and goes out with the bidders. At the end of the day, I want to sync all 3. I may enter info throughout the day on my desktop (which needs to sync up to the other 2) and vise versa. Using SharePoint is not an option as the Notebook and Laptop will not always have internet access.
View RepliesI have a database for managing time related contracts.
I need to be able to run parameter queries but if I query the data by start date then and contracts which are current but started before the first date entered in the query (this is the same for end dates) are not shown.
I am attempting to create a database for managing nonconforming product internally.
Different stages will need to be assigned to an individual within the organisation, is it possible to have a lookup value in my table which draws its information from active directory?
All the users are obviously in here and managed already, I dont want to have to create and manage a separate list of users just for my database.
I created the database on my computer. I added security and split the database to put the tables on the the server. Everything works just fine on my computer. However, when I try to copy the 3 files for the front end of the database to other computers on the network, it won't work.
Perhaps I'm confused as to what files need to be migrated to each additional computer or I don't have the security setup properly.
Right now, I move 3 files. The main database file with the front end. The shortcut file that is created when you add security and the security file that is created when you add security to the database.
I'm stumped.
I've designed an Access Database (using Access 2003) and assigned user-level security to it such that some users are read only and some are full access. Everyone requires a login and password to access the database. The problem is, once I copy/paste the database from my computer to the shared drive (where it will be accessed by all the users) the prompt for a username and password disappears.
View 3 Replies View RelatedWe're a small shop building a modest database to keep track of our product listings on eBay. Two people on non networked computers are doing the data entry into cloned copies of our database. This includes attached photos for each record. At the end of each week, we want to combine the records created separately into the master database on my system.
It seems like it would be simple enough to just cut and the paste the records from the two databases into the identical master. The problem I'm anticipating concerns the attached photos.
Some strategies are to transfer the records from the data entry computers to the master database while keeping the attached photos intact.
Is there an effective, efficient way to set this up or would it be best to use the OLE format for the pictures instead of attaching them (which I prefer)?
I have source data in a table in an excel sheet.
Amongst other things, I need to store a copy of that data in a table on a sql server database.
How can I sync these?
Here is what I have come up with,
Q1 delete from local table
Q2 append local table from excel data
Q3 delete from sqlserver left join local data where local id is null
Q4 append local data left join sqlserver into sqlserver where
sqlserver's id is null.
I do the Q3/Q4 delete and append, because copying the full table takes aorund 20 minutes, but in reality only about 100 records change from day to day, and Q3/Q4 runs about 2 minutes.
This works, BUT, it is possibly that the excel data could be empty, at which point, I would want to keep the old data on the sqlserver. The sequence above would erase the old data.
So two questions
1) is there a way to sync the excel source with the sqlserver destiantion without using the local copy?
2) Is there a way to preserve the sqlserver data if the excel file is empty?
Thanks,
David
use of a query, or a report or it could involve changing the table structure.Say I have a many to one relationship, for simplicity we'll call the table on the 'many' side tblThing and the table on the 'one' side tblGroupofThings.When managing the records in these tables it is important that each Group of Things contains at least one Thing, but it isn't important that each Thing is part of a Group of Things.
Things and Groups of Things are constantly being added to the table. As I see it, the only way to manage this is from the Things table, or at least a form based on the Things table, as I am editing a foreign key for tblGroupofThings inside tblThings. However it would be easier for me to manage it from the Groups of Things end, so that my workflow goes as follows:
-I add a new Group of Things to tblGroupofThings,
-I then 'put inside' that Group of Things, the Things that belong inside it (i.e. make the foreign key field in those Things point to the Group of Things.
Managing from the Things end means I have to start of with the new Groups of Things which are 'empty' at the moment, decide which things need to go in them then swtich to Things and remember which Things need to go into which Groups of Things.all the talk about putting records inside other records when I'm really talking about foreign keys. I know that's not know it really works but to the user that's how it should appear to work.
I am trying to send the current record and the current subform record to Reports for printing.Each record on the Form can have multiple records in the subform, however I only want to print the Main Form and the Current subform.Form / subform are linked on EquipID and EquipIDfk and are working correctly.Report / subreport are linked the same way and work correctly.When I try to use the DoCmd.OpenReport... It is printing the data from the main form plus all subform records related to the main record.I have tried the following Sub to filter the subreport without success.
Private Sub cmdPrintRecord_Click()
Dim strWhere As String
Dim strLook1 As String
Dim strLook2 As String
strLook1 = Me![EquipID]
strLook2 = Me![subfrmInspectionReport].Form![InspectionFindingspk]
[code]...
Using Debug strLook1 and strLook2 both show they have the correct relevant record numbers and the value stored in strWhere is: [EquipID] = 745 And Reports![RptPrintRecord]![subrptInspectionReport].Report![InspectionFindingspk] = 8. This only prints an empty report. It seems that when I try to reference the subreport it goes pear shaped.
I have a list box populated with the movie title of my movie database. The form, which is based on the same table, shows the movie description in a memo field. Currently, in order to sync the list box with the main form, I'm using the following code in the AfterUpdate of the list box:
Code:
Private Sub lstDvd_AfterUpdate()
Dim rst As DAO.Recordset
On Error GoTo lstDvd_AfterUpdate_Error
If Not IsNull(Me.lstDvd) Then
Set rst = Me.RecordsetClone
[Code] ....
It works fine when I select a row in the list box. But, when I use the navigation buttons at the bottom to move to another record, only the main form moves to the next record (obviously). How I might sync the list box and the form when I move the form to another record?
I'm writing a task tracking database to be used during staff meetings. Each task should be assigned a priority, and no two tasks should ever have the same priority. Easy enough.
Here's where I could use some suggestions. If I have a new task, what would be easiest way to manage that insertion (assuming it has a higher priority than the lowest item), without having to renumber all the other items in the table?
I work for a firm who have a few thousand clients and we are wanting to go through a process of checking with them that the data we send them is going to the email address they would like it to go to. However in some cases we don't currently hold an address so we are contacting them by phone to get the addresses over the next few weeks.
The way I've chosen to do this is to use the excellent link between Access and Outlook to send an email to the address we have on file (or have gotten) with a very light form they can optionally fill in to update the email address if desired.All seems well in testing this with 2 exceptions:
1) I have 2 email boxes in outlook, my "personal" one and a group mailbox. I'd like the emails access sends to come from the group mailbox but cannot see an option to control this.
2) We're likely to send the emails out in tranches - is there a way to have a single table the drives the email process but some how have it know that we have sent the emails out for certain clients such that when I send a second tranche, it doesn't re-send to clients that have already been contacted?
I have two tables,
tbl_Retainer
tbl_Retainer_Grant_Funding
tbl_Retainer has the field,
Retainer_ID
And tbl_Retainer_Grant_Funding has the fields,
Retainer_ID (a lookup field from tbl_Retainer)
Agreement_Num (a lookup field from tbl_Grant)
I have a form based off of a query(not sure if that matters), that uses that tables, tbl_Assignment and tbl_Assignment_Grant_Funding. These tables have the above fields as lookup fields.
So...what happens is, if an Assignment has a Retainer, I want the Agreement_Num box to show only the Agreement_Num's associated with that Retainer, otherwise just show all the Agreement Num's.
In my form, I have Retainer_ID with the row source,
SELECT tbl_RETAINER.Retainer_ID FROM tbl_RETAINER;
And Agreement_Num with the row source,
SELECT [tbl_GRANT].Grant_ID, [tbl_GRANT].Agreement_Num FROM tbl_GRANT ORDER BY [Agreement_Num];
In my AfterUpdate event for Retainer_ID I have,
Private Sub Retainer_ID_AfterUpdate()
Dim strSql As String
strSql = "SELECT [Retainer_ID]," & _
"[Agreement_Num]," & _
"FROM tbl_RETAINER_GRANT_FUNDING" & _
"WHERE [Retainer_ID] = " & Me.Retainer_ID.Value
Me.Agreement_Num.RowSource = strSql
Me.Agreement_Num.Requery
End Sub
When I am in my form and choose a Retainer ID, the Agreement_Num box goes blank, and there are no choices to choose from. I am wondering if this is because the Agreement_Num's are sourced from tbl_Grant and not from tbl_Retainer_Grant_Funding.
Having had little luck in getting any concrete information about effective ways to manage ODBC data, and after days of long trials and errors, I thought at least I could write up a summary of what I've found to work well for ODBC sources and hopefully others may be able to contribute to this.Disclaimer: This is a far cry from being an authoritative and is woefully very subjective, being written by me and myself, using a MySQL server and Access 2003. I do hope that others who are able, can contribute more information to make this somehow more useful for those who would like to use Access as a front-end client.There are three principal issues that must be considered when you are using a ODBC data sources:1. Numbers of connections and different flavor of connections.2. Size of recordsets and network traffic.3. Binding forms to ODBC data sources.As a starting point, one should read the whitepaper on Jet and ODBC Connectivity. (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp ) This was for Jet 3.0, but should be valid for 4.0 as well. ODBC Driver and ConnectionsThe next thing is to understand what your ODBC driver is capable of. In a connection string, there is a parameter for "Options", which is usually a long integer. You should look at the ODBC driver's manual to ensure you have all options you need turned on. A good example would be to ensure that a certain data type is correctly mapped to Access's data type. In my case, I ensured that Big Integer (64-bit) were turned off because Access does not support this (and thankfully, I don't need it anyway). By far most important thing you want to look for is to ensure that the driver supports two things- Multiple statements and Active statements. Jet does try to pool all queries to the server into one connection whenever possible, but if the driver cannot support multiple statements over single connection, Jet will open another connection to submit a statement. Therefore, if you have a form that has a combobox, Jet will send two queries, one for form's recordsource and another for combobox's rowsource. In case of lack of support for multiple statement, Jet will need two connections. Even if your driver supports multiple statements, Jet may find it necessary to open a second connection if the driver cannot have more than one active statement. Active statement is when you fetch a big recordset and need to wait a bit for the full set to come over the wire. So back to that form with a combobox, if Jet find that the form's recordsource will take a while to be fetched, it will go ahead and open second connection to populate the combobox in order to allow for 'instantaneous' loading of a form from a user's POV.Wherever possible, keep numbers of connection to a minimum. As mentioned before, Jet will try to pool all statements into one connection wherever possible, but Jet cannot help you out if you issue a query using a new ADO connection, DAO ODBCDirect workspace, or create a recordset within VBA. If you want to avoid the additional overhead of another connection, you should use a stored query instead, and make sure it has a Connection string in its query property window set, so Jet knows that it uses ODBC data and pools it with its open connection to the ODBC server. The stored query can then be called within VBA without incurring another connection.DSN and DSN-less/ODBC and OLE DB connectionsThere are two ways to create connections, by using Data Source Name (DSN) or direct connection. To find right connection string, look at Carl Prothman's excellent website listing all possible connection strings (http://www.carlprothman.net/Default.aspx?tabid=81). Some people say connecting to DSN is better than without. Others say it's faster without DSN. One thing for sure is if you use DSN, you will need to distribute the DSN file to your users along with the finished database. I would recommend experimenting with both connection flavors before deciding which is better suited for your needs.To make a DSN connection DSN-less, look at Doug J Steele's example (http://www.accessmvp.com/DJSteele/DSNLessLinks.html) or perhaps this Add-in by Paul Litwin (http://www.mvps.org/access/modules/mdl0064.htm).Also, make sure you know whether you have different drivers available. I know for an example, some people prefer third-party drivers for Oracle over one supplied by Oracle themselves. Furthermore, some drivers are OLE DB which is supposedly better than ODBC (I say supposedly only because I am skeptical of Microsoft's promises of new and latest technology superseding a older technology, then dropping their claims and going back to older technology). Binding formsTo Jet's credit, it is quite intelligent in retrieving just enough rows to populate the bound form, and will continue to fill up the recordset periodically while a user is working on a row. However, Jet has some quirks where it can do something very stupid, such as asking for multiple full table scans. If you bind a form to ODBC table, Jet will do a full table scan. The simplest solution would be to add a WHERE clause to make the recordset smaller. But by far the better solution would be to set the recordsource's connection string to use ODBC instead of "(current database)". Jet will then query for keys, which it must have locally in order to manage a recordset, then afterward query a few rows at a time. If you can manage to keep keys smaller, all the better.Furthermore, if you want to use subform, do not use Master/Child linking fields. This makes Jet go ape-shit, issuing several queries to show tables and index which is quite unnecessary. Rather, leave the link blank, and set subform's recordsource with a WHERE clause to match the parent form's key, so Jet will only ask for rows that match the key only without asking for indexes and table status every time you move around.An additional benefit of making all queries for forms' recordsource an ODBC query is that you now have more control over how you handle those forms *with* Jet's intelligence. For example, you now can start transaction across multiple forms with subforms and commiting/rollbacking as you see fit, which would have not been possible using Access itself. I have been able to rollback the changes in two parent records and their related child records by issuing a SQL Pass-through query which simply says "Rollback;" and nothing was changed for any one of records, just as expected. If you have a combobox or listbox on a form, this will mean another query in order to fill in the rowsource. Ideally, you want to keep some tables local to front-end clients, especially for tables that will never change (e.g. a list of states or provinces for example). For tables that may be updated peridocially but is otherwise select-only, you need to decide whether you want to make it a dynaset or a snapshot. For a small set, snapshot is faster, but for a larger set, dynaset is faster. You will need to experiment with the rowsoource to ensure that the network traffic and time to load the rowsource is satisfactory. One problem is that Access won't accept an variable (at least I have been unable to do so) for a stored query's connection string, so if you need to change a connection string (e.g. you want to use different set of options, perhaps?), you would need to do this by hand, or at least write a function to loop through queries and updating the connection strings. Haven't tried that yet, but would imagine this is very possible.Keep an eye on SQL log when developingYou definitely will want to have the server write a log of what queries it has received from Jet to give you feedback in ensuring that Jet doesn't do anything stupid. This has told me far much more about Jet than working within Access environment. Unresolved issuesThere is only one thing I haven't yet worked on- Sharing a recordset for different controls. Suppose we have a form with a combobox and a subform, both which use same table as a rowsource and recordsource, respectively. In this case, Jet will issue two separate query to the server, even if they may use same recordset. If anyone has been able to show how to get such controls to share recordset, that would be cool.Also, I'm a bit worried about scattering connection strings all over the place, especially that it will contain a password. As I see no point in asking users to authenicate themselves every time they use a query with a ODBC connection string, given that variables can't be used in query's connection string. Would MDE make this less of a problem (I do not know if password still can be plucked out from a hex editor?). A possible solution is to synchronize Access's security with the server's security, because Jet will try to use Access's user & password for initial connection, and if a call to retrieve password was made along with a module at startup to 'fix connections' along with updating the connection string with the entered password, this may help somehow with keeping the password secret? Does anyone have more information on that?External LinkA useful FAQ (http://archives.postgresql.org/pgsql-interfaces/2000-07/msg00193.php) for linking Access with Postgresql which may be useful in giving you some ideas of how you can work with Access. If anyone wants to contribute to this, please do feel free to do so. If anyone finds anything that is dead-on wrong, please give me a good can of whoop-ass- I hate to lead blind into ditch, so to speak. :)
View 4 Replies View RelatedI have been looking for a way to sync a table holding reservation dates to MS outlook calendar automatically when dates are inserted in the table.
View 2 Replies View RelatedI have a program thats work perfectly in some computers useing the network but when i try to open it in other computers i get an error and when i try to debug the error it highlit the line that i am calling the micro that open the mainform in it, i hope some one have an idea for what is going on with the program
View 12 Replies View RelatedHey, My access database will be used to make computer reservations for an internet cafè. The user makes their reservation date by using a calendar control, they choose their start and end time, and they also choose the amount of computers they can book (no more than X). Would it be possible to create a query that will limit the user from overbooking the computers?
View 14 Replies View RelatedWell...here's the deal. Four computers were replaced in our office and now three of them are having problems with the database. The database is written in Access2003 and all computers (the old ones and the four new ones) are running on WindowsXP Professional w/ Office 2003.
The problems that comes up only involves reports (viewing, email, exporting). When they try to view a report, a message comes up
Cannot find object ".
When they try to email or export a report the function is cancelled due to the error.
I'm going to have the computer admin re-install Access to see if that helps.
I've been searching around trying to find some info on various settings that would cause this, but I'm pretty ignorant in this arena. I checked the references in Access and they are both the same. That's about the extent of my knowledge in thise area.
Any insight would be great.
Thanks for the help.
BB
Hello All,
I have MSAccess ade working in some computers which has WindowsXP-pro(SP1 and SP-2) without any problem. But It doesnot work(When i said it doesn't work means it doesn't work in certain parts which i will give an example) in some computers that has exact same configaration. The problem is when I try to instance a class using a TypeName function, it doesnot recognize the class name and just returns an object rather than recognizing the class's name. Its a strange thing because I have some .ade from past works fine in all machines. The only difference is In the new .Ade is I have included the Microsoft outlook 11.0 object library. But the part of the code thats not working doesn't even use the Outlook library at all. So did anyone has this kind of problem, PLease suggest or ask me if you have more details of this problem. I really appreciate your help.
Thanks again
ds111
Hi,
Hopefully this is a really simple requiring a simple answer.... hopefully
On one of my forms I have a combo box thats values are generated by a parameter query. On my computer and a couple of others i tested it (on the same network) this combo box populates fine, but i put it on a couple of other computers today and it doesn't show any values. I checked and the query itself runs fine, plus a combo box that runs off a table populates fine.
not too sure if its relative but the database is split with the tables sitting on the network share and the database sitting on the main computer. or perhaps this could have something to do with blocking unsafe expressions.. i am not sure
any help on this would be greatly greatly appreciated
I have 2 tables tblComputers & tblComputerAllocations
tblcomputers has 85 records & tblComputerAllocations has 71.
I would like a query that returns the 14 computers that have no allocations.
I tried a union query on field ComputerID but i must misunderstand its use!!
Thanks in advance,
Phil.
Does anyone know/how my forms' background colors are different from one computer to another? The computers that are showing the correct color are both XP and Access 2002 (one is a laptop and one is a desktop)... but then someone else.. on a laptop (with XP and access 2002).. the forms' background colors are not good (purple)... vs. blue on my laptop and the desktop. Thanks!
View 3 Replies View RelatedI have a report that was created on my computer. When this report is opened on other computers (2 that I've tried) the text alignment is way off. Originally I thought it had to do with printer margins dictating something but that is not the case as the margins remain identical on all computers. It's not a font issue because it's Arial which all computers have. The text is written inside a "label".
View 5 Replies View RelatedI design/maintain an Access database for a friend's business using my home computer. The printer he uses allows for much smaller margins than mine does, so anytime I edit a specific report that uses very small margins, my computer automatically increases them to the minimum margins my printer allows and when the business owner runs the report on his computer it spills on to extra pages.
Right now every time I make a change, I need to go directly to his computer and adjust the margins within the report after the new front end is uploaded.Is there any way around this, such as telling Access to ignore the printer's minimum margins?
I recently completed a database project and was set to launch it today for use throughout the company. The database is split, and both ends are saved in the same folder on the company server. I have copied a shortcut to the front-end file onto the desktop of each of the four user computers. Two of the four users are able to open and operate the front-end with no issues. The other two won't run the 'Default Value' formula for the "Order#" field in the front-end form. This being the case, I'm certain this is not a code issue.
Form
- Field
- Control Source
- Default Value
PO#
= [YEAR] & ["P"] & [Order#]
N/A
[code].....
When I opened the form on the two noncompliant computers the PO# field displayed #Type!.I deleted the Order# default value to confirm that that was indeed the source of the problem, and it is. When deleted, the PO# field displays "13P" instead of "#Type!".
I've tinkered around with Trust Center to no avail. And I'm almost certain this is not an upgrade/patch issue as one of the nonconcompliant computers had Access 2010 downloaded yesterday. The server and all users have Access 2010.As well, the option settings on the front-end are set to only open the form, with no editing capabilities. However, when using Shift+Enter to open the edit capable version, there is no issue; the PO# populates.
I have completed a database for a company with 60 000 clients and over 100 000 job records.
The database works at very resonable speeds on the Server computer, or the computer on which I installed the back end of the database.
Each of the other 3 computers on the wireless network, have a local copy of the Front End on their machine, and reference the Back End (BE) on the server computer.
Each of the Client computers have varing speeds when accessing the BE, some as slow as 10 minutes for a simple search, filter or just loading a form.
What can I do to improve performance across the network?
Do I need to install additional components on the other computers to improve the db performance?
Your help will be much appreciated.
Joseph.