Code To Link From Access To Company System
Oct 21, 2004Through word of mouth I hear that you can creat a link that can go from Access and link to the personal company system. Is this true? If it is, is there a standard code to use?
View Replies
ADVERTISEMENT
Queries :: Importing Data From Clocking On System To Analyse Worked Hours - Link Paired Record?
Jul 5, 2013I have been tasked with importing data from a clocking on system to analyse worked hours.
There is no option to amend the format of the export which is not great
surnameforenameField2Field3xxx
xxx
11/06/201319.18
xxx
xxx
11/06/20135.37+
xxx
xxx
12/06/20135.41+
xxx
xxx
12/06/201319.21
The plus indicates a night shift worker
eg started at 19.18 and finished at 5.37 on the 12th
so for every record I have staff id , date, and clock time ( I have stripped out the + ) and created a yes/no field to identify the records where field2 should actually be field2+1
I have sat in query design screen for ages and cant think how on earth I am going to calculate hours worked for a given staff member and date combination.
for every combination of staff id and date there should be 2 records - a clock in and a clock out
I thought about creating new fields clockin and clockout but struggling to see how I can link the 2 "paired" records together
vb script with dlookup for every record ?
Web Hosting Company That Supports Data Access Pages
May 30, 2006Hi. Does anyone know a web hosting company that supports Data Access Pages? Thank you.
View 4 Replies View RelatedGeneral :: How To Share Access Database On Company Intranet
Jan 14, 2015I want to consider ways of sharing an Access database within our company. This could be web-based via our intranet or done some other way.
I understand that Microsoft's preferred way of sharing via an Intranet is to use SharePoint. However, as we don't use SharePoint (we use Atlassian Confluence) the cost of that route would be prohibitive.
Any methods of sharing an Access database across, say, 20 people? Simultaneous read/write access would be required but in reality the usage will be low, so performance is not really an issue. I would like to consider web-based or other methods.
I have chosen Access 2013 simply because we use Office Professional 2013 and Access will be familiar to some developers. We could use a different product. But I would certainly prefer a tool that supports RAD design of forms.
Help With Link Code Please...
Dec 28, 2007I have a FE database that on initial setup the user will enter the drive letter of the mapped location where the BE resides. The FE will then update all the linked tables to that path. This should work but for some reason the FE locks up (stops responding) when I test this. Any suggestions as to why. It doesn't stop responding until this part of the code:
tdf.Refreshlink
I have looked and it does change the link path, but it locks up.
Dim dbs As DAO.database
Dim dataPath As String
Dim tdf As DAO.TableDef
dataPath = Me.drive & ":IMP1_R2.2_be.mdb"
For Each tdf In dbs.TableDefs
'Not a MSys type table
'If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
If InStr(tdf.name, "MSys") = 0 Then
tdf.Connect = ";DATABASE=" & dataPath
End If
err = 0
On Error Resume Next
tdf.RefreshLink
End If
Next tdf
DoCmd.Echo True, "Done"
Link Tables Via Code
Feb 7, 2007Hey everyone I'm new here but pretty familiar with Access etc. I am extrememl;y stumped on this and I have never attempted this so any answers and details would be helpful. As we all know this is a learn new things as you develop type of application or at least for me.
I have a database that exists in one building and is functionnig fine using linked tables (File/Get External Data/Link Tables) but I now need to make this same application work for multiple locations which each have its own tables that are seperate from each other location. I was thinking a Main form that opens with radio buttons and based on the one you select would determine which tables it would be linked to when it opens. I figured Radio1 be SEDC, Radio2 be SWDC.
I have a database I have setup as a storage database for just the tables for each location that are on different servers in each location to increase traffic speeds.
Example:
SEDC (RadioButton1)
SEDC Database is located on a erver with 50 different tables in it "//sedc1/data/shared/warehouse/SEDC Warehouse.mdb"
SWDC (RadioButton2)
SWDC Database is located on server with same tables in it but different data for that location "//swdc1/data/shared/warehouse/SWDC Warehouse.mdb"
So on So on...
Broken Link To VB Code
Sep 13, 2005I am having trouble with a simple data base where I have 1 main form with 1 sub form, but when I try to use the form (go from design view to datasheet view) I get the message
Error Accessing File
Network Connection May have been lost.
and the link between the form and the code seems to have been lost ... I can no longer edit the vb code (it keeps reverting back no matter what I type).
When I delete the form, the VB entry remains - (since it is no longer linked with the form). Running the database this way I get error messages saying there is a Macro with a misspelled name (ie: it can't find the form with the name).
The only way I have been able to get rid of it is to create a new form with nothing in it, save it and then delete it.
Really frustrating!!!!
Any ideas on how I can get rid of this problem, best ways to re-build the data base (I have created a new one, imported the tables and created new forms from scratch ... then this problem comes back@&^^#%$@).
Thanks
Link Table With Code
Jan 19, 2005Hi
I am linking av table from Sybase Adaptive server anywhere 8.05 by using this code:
DoCmd.TransferDatabase acLink, "ODBC", "odbc;DSN=database;UID=dba;PWD=sql;DATABASE=databas e", acTable, "trc.ansatt_dato", "trc_ansatt_dato", False
This works just fine, but I gety this dialogbox where I am asked to select a unique keys or something.... .
Is it possible to write a code to automatic choose OK?
Anyone?
eroness
Building A Link To Table In Another Db From Code
Mar 4, 2005i have two tables that only need to exist for the length of the user's session. i build them with ADOX in my backend database when the user starts up, and destroy them with ADOX when the user closes the application. the name of the table created varies, too, depending on the user (userID is built into table name).
i need combo boxes on my forms in the front end to be able to access this data.
is there a way in code, once the temp tables are built, to create a 'link tables' type of setup?
my alternative is to build the temp tables in the front end portion, but i don't really want to do that.....
Code To Link Text File As Table
Jan 29, 2008I'm trying to figure out how to link a text file to my database with VBA. I know how to do this for other types of data sources, using the DoCmd.TransferDatabase acLink command, but need guidance using this with TXT files.
Thanks,
Randy
Code To Link Tables - Overwrite Specific Links
Oct 13, 2006I searched and couldn't find anything on this specific issue.
I'm on the verge of designing a pretty big database and I've run into what may become a bit of a problem. There will probably be several backends in different locations. Should another backend ever be created or if the location for one changes, the user can use a form to refresh the links to the tables on that specific database.
The way I've done this so far is by having a piece of code that checks the tables for a connection string. If the table has a connection string, its a linked table, so the link gets deleted and replaced with the new link. Well this will no long work since there will be several backends and only 1 of them may need its tables relinked and using that code will delete all the table links, including the good ones.
Is there a way to make 'DoCmd.TransferDatabase' overwrite table names instead of giving the duplicate tables a number suffix? I think that doing this would be easier than retrieving the table names from the new backend and comparing them with the linked table names in the frontend so the old links could then be deleted.
Tables :: Primary Web Based Inventory System - Update Raw Data From Another System
Mar 13, 2014I have our primary web based inventory system that I am exporting to Excel and using this as an import to Access for the main raw data for my database. This being inventory it changes daily so I am updating this table every day. When I try to append the table it ads all the records. I am wanting an easy way to add only the new records/take out the ones that are no longer there. Basically update the table with what is currently there.The only have I have found to do this is by running non-matching queries and update queries.
View 2 Replies View RelatedGetting Into An Access System
Jun 12, 2007Someone, who is no longer working at out organisation, created a system in Access which we are trying to get into, however the creater put on some security which will not let us open the system to alter. Is there a way of getting into this?
View 11 Replies View RelatedAccess Quiz System
Jul 29, 2005hi
i am trying to make a quiz system using ms access
i want to select 30 questions randomly from a questionbak of 100 or more
also i need to select 3 answers randomly including the corect answer
from answer bank that has 5 possible answers for each question
tblquestionbank
QID Question
1 Question1
2 Question2
3 Question3
tblanswerbank
AID QID ANSWER CORRECT
1 1 Q1 answer a true
2 1 Q1 answer b false
3 1 Q1 answer c false
4 1 Q1 answer d false
5 1 Q1 answer e false
the field correct is for identifying the correct answer yes/no filed
so far i have been able to get the questions randomly
that is generating a random number in the query and selecting
top 30 records
ideas and help would be appreciated
Implement Barcode System With Access
Feb 1, 2006Sorry for the flashy post.
1.st Job:
I have an access 2003 db. I want to implement barcode system to the DB in which I can print barcodes in any kind of barcode printer and also when imputing an order read data from barcode scanner etc.
2nd Job:
I want to put access db to a server so I can view reports, forms, imput data and retrive data from internet explorer window (with a password).
I will give my DB to you so you can work on it. Please do the pricing seperate for each job.
Please PM or Reply if you have more than 500 posts. PLEASE RESPOND IF YOU REALLY KNOW HOW TO DO IT.
Thanks in advance.
Access 2003 Using System DSN Problem
Jun 20, 2007I have a system DSN, pervasive ODBC engine interface. In Access 2003 I can link to the DSN but can only link to some of the files. This started when I changed to a new computer. Before, I had a link to the same database using Access 2002 with no problems.
Any help will be greatly appreciated.
Brad
Access System Size Recommendations
Jan 3, 2008Hi,
I have been given an Access database to maintain and it has some performance issues.
I have been looking through this forums for recommendatons regardng size etc but didn't really find anything.
It is all in one file (might consider splitting it..) and it has about 350 forms, 300 database queries, 130 database tables and 200 Macros!! Filesize something around 200 MB.
In one of the forms there is a drop down that when changed refreshes two other dropdowns. I have chyecked the queris used and they are really fast but it still takes at least 6-7 seconds for the 2 dropdowns to reload!
I don't know if it is due to the way it is done, the VB code calls a macro that calls a query.
Personally I wouldn't have done it like that but there has been about 2 years since I did anything complicated with access..
Or is it the size/complexity of it that makes it slow? Does anyone have experince of a similar system?
Thanks /H
Access Errors And Crashes On New System
Jul 24, 2012My office computer, along with the phone system, printers etc took a lightening strike last week. The hard drive survived but not the computer. I was able to get the office access db onto a new system but now I get errors when running it. When opened, the main menu appears. Whoopee!Not so fast. When I select an item, I get "the expression On Click you entered as the event property setting produced the following Automation error. The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]There may have been an error evaluating the function, event, or macro"
Pressing the button a second time does not produce the error and opens the correct form.This form works properly. The second problem is with a second report form that opens properly.This form expects dates and accepts them but when I try to print the report, access closes with no error message.
Legacy System Access 2.0 Migration Queries
Jun 12, 2006First post, so I hope I'm following the post-etiquette!
Anyway, I've just been employed by a company who still uses access 2.0 and lotus smartsuite.
Basically I'm gonna have to migrate a few of their backbone databases to access 2000+
I've managed to find the old Microsoft access 2.0 book in the company amazingly, which is a help.
I was just wondering if anyone knows any good sites for migration, or any particular problems that may be encountered. I'm just doing some background work at the moment, this won't be happening for a few weeks (hopefully!)
Any help would be greatly appreciated.
I'll just take this opportunity to say that I've found the site very useful in the last few weeks and hope I can contribute in the future when I break out of newbie status!
-Spud.
User Authentication System For Access 2007 Questions
Jan 1, 2008Alrighty -
After learning that 2007 has no User Security roles, and not having Sharepoint or a SQL server, I decided to work starting with Bob's Simple Login script located here (http://www.btabdevelopment.com/main/AccessSamples/tabid/54/Default.aspx).
I've got it functioning fine and incorporated some of the options also made available here (http://www.databasedev.co.uk/login.html).
You'll see the code below used to store info in a hidden form that is holding the username and permissions level. I'm looking to try and store this information into a global variable instead of a hidden table.
I know that I could define it as a variable right here in the code, but how do I define it as a Global variable so I can use it later in the application in the VBA?
Private Sub cmdLogin_Click()
Dim strUser As String
Dim strPWD As String
Dim intUSL As Integer
strUser = Me.txtUser
If DCount("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'") > 0 Then
strPWD = DLookup("[UserPWD]", "tblUsers", "[UserName]='" & Me.txtUser & "'")
If strPWD = Me.txtPwd Then
intUSL = DLookup("[SecurityGroup]", "tblUsers", "[UserName]='" & Me.txtUser & "'")
Forms!frmUSL.txtUN = strUser
Forms!frmUSL.txtUSL = intUSL
Select Case intUSL
Case 1
DoCmd.OpenForm "frmHome", acNormal
Case 2
DoCmd.OpenForm "frmHome", acNormal, , , acFormReadOnly
Case 3
MsgBox "Not configured yet", vbExclamation, "Not configured"
Case 4
MsgBox "Not configured yet", vbExclamation, "Not configured"
End Select
DoCmd.Close acForm, "frmLogin", acSaveNo
Else
If MsgBox("You entered an incorrect password" & vbCrLf & _
"Would you like to re-end your password?", vbQuestion + vbYesNo, "Restricted Access") = vbYes Then
Me.txtPwd.Value = ""
Counter = Counter + 1
If Counter = DLookup("[OptionValueNum]", "tblOptions", "[OptionsID]=1") Then
MsgBox "You have entered an incorrect password too many times. This database will now close!", vbCritical, "Wrong password!"
DoCmd.Quit
End If
Else
DoCmd.Quit
End If
End If
End If
End Sub
Forms :: Referencing A Control On Access 2010 Navigation System
May 29, 2015I have an Access 2010 Database frontend to SQL Server 2008 Backend. So far there are 3 Tabs in the Navigation System. The first one is the primary Data Entry form which has a tab control with 3 subforms.
Here's the problem. The database is designed to track potential members based on different types of Ads. The primary Data Entry form holds all of the demographic data for the person that called in response to an ad. The first tab has a subform to track how many times that person has contacted us. The second subform tracks which advertisements the person is contacting us about and the third tracks which Events that person attended. There is no correlation between contacts, Ads and Events.
On the Advertisements we have about 7 different types of Ads we do and each ad can be run multiple times on different days. We want to be able to track which Ad the person contacted us about. The Advertisement subform has a comgo box for the Ad type and a combo box for the ad date. the source for the combo box for the ad date includes the combobox for the adtype as the criteria. That criteria is what's giving me the problem. I've used:
forms!mainmenu!navigationsubform.form!potential_me mbers!frmAdvertisements!cboadvid
Along with every other variation I can think of. The form itself is frmAdvertisements but I'm using it in a tab control and I also tried:
forms!mainment!navigationsubform.form!potential_me mbers!adv!cboadvid
Queries :: Unable To Convert Text Into Date With Different System Language Settings In MS Access
Jun 6, 2013I have a table in access database which contains a text field 'EDate' that stores Date value in format (12-Apr-2013). Now I want to run a sql query on that field. User will give an input date. The sql query needs to fetch me all the records from access database whose Edate is less than or equal to the user input date.
I am using DateValue function to convert my text filed Edate into date. My query is something like this:
select * from table_name where DateValue(EDate)<='user_input_date'
I am able to perform above task if the system language settings are 'English'. But if system language settings are different say Turkish, then the query fails.
I searched a lot on web and found that DateTime function compares test data with the system date time format and gives the result. Thus it fails with different language settings.
DB For Rental Company
Dec 26, 2007My company rents equipment to our customers. I am looking for a db to track our stuff. There are three possible locations of everything. Warehouse, employee trucks, and Customers. I assume that warehouse can be added to the employee table. I can set up these 2 tables and one for our equipment list, but the hardest thing I am having with is with the table for transactions(I believe it should be a table), this needs to allow batch transations, because typically more than one piece of equipment is moved at a time.
I will need a report that will show each equipment id number in a customers home from date to date (date it was placed and date it was removed), one that will show a list of all equipment locations on a given date, and one more that will show equipment that has been in a customers house for 5 days or more so I can verify that it is still there, becuase equipment typically should never be with a customer for more than 5 days.
Right now I am using excel, but its not working well. I would greatly appreciate some help! The attachment only has the info I need for the 3 tables I mentioned above that I know how to do, and my best guess at what the transaction table should look like.
See atached.
Company Database
Apr 27, 2008Hi.
if anyone has the MS Access database which includes the Employee, Project, Department, Works_on, Dependent tables, plz post here...
Thanks.
Showing Primary Key As Company Name
Sep 28, 2005how do I set up supplier name in the order table like the example in the
northwind database?
what I mean is set supplier ID so that it shows combo box with names instead of numbers.
How To Pass A Company ID Thorughout All Forms
Jul 5, 2005Hi,
I have a database that controls donations for a Charity. There is to be a sister charity added to the database and a lot of the previously created objects can be used to provide for the new Charity.
My idea was to create a "company" ID and label the categories of the donations. I could then use this company ID to identify to whom the data captured belongs.
I have used the switchboard manager to enable navigation, in this I have a ONload event procedure that requests the Company ID (1 or 2).
That's as far as I have got.
How would I get Switchboard to open each form (some being based on queries - a couple direct on the tables) and filter the data on the company ID?
If there is a better way, please enlighten me!! :)
Many thanks in advance
daz.