Auto Login Linked Tables
Nov 21, 2007
We have two back end SQL Server databases accessed by VPN to a remote location using RDP locally. It is common to use MS Access as a report generation tool oftentimes containing linked tables from either or both db's and having Reports/Forms/Queries utilizing joins against tables from both SQL databases. Having to enter User Name and Password twice each time is more than a pain.
First a user would not have access to the MS Access report FE without first having appropriate and successful login UN/PWD to the remote server. Therefore having to login again when instancing reports/forms/queries against those linked tables/pass through queries is a minor pain. How or what process is used to tell Access to never prompt for User Name and Password? Or is there one? Kind of like telling MS Query tool in Excel to 'Remember Password'...
TIA
View Replies
ADVERTISEMENT
Apr 17, 2015
I have a ms access application in Front End and linked tables to sql server 2008 r2 in back end.. every time I open my application the linked tables ask to me for the user and password of the sql server user of the odbc conection. I want to connect to my sql database without login every time the user open the application, I know that can be possible if I start session from vba code...
View 2 Replies
View Related
Sep 21, 2013
I have created quite a substantial and effective database for a small gliding club with all the major data tables being linked. I need to be able to re-index tables periodically so that running totals, which are needed to calculate statement balances, works correctly - all well and good. The only problem is that the process of re-indexing requires the data in the main table to be stored temporarily and the original data deleted. When the temporary data is appended to the main table the auto-numbering just keeps clocking up.how to reset the auto-numbering in a local table.
View 3 Replies
View Related
Nov 29, 2013
I am really new to Access 2010 as I normally use SQL so im sure this is a very basic question. I have a form where I have linked some sql tables to a table in access so the data gets stored in SQL. The form consists of many fields the basic fields are Policy ID, Name, Office, Month, Dept etc. At the moment users have to fill this in using comboboxes and text boxes but I want to change this so when the user puts in the Policy ID all the other fields are populated ie in the office field it would have a sql query of or something that looksup the office from the policy id that is entered:
SELECT [Office] FROM DBO.DQ
WHERE [Policy_ID]='what is entered into the Policy ID field' and so on.
Also not sure if this works but as I have now linked the tables to SQL does this mean that when a user fills in the information into the form it will then update the SQL table or is this another issue?
View 1 Replies
View Related
Jan 6, 2015
I understand right off the bat if you're reaction is "don't duplicate data!!" -- mine would be too (don't fret, I know my normalization).
I've linked a table in my db to my Global Address Book in Outlook 2007 and, upon entering an employee number as a new record, would like to verify that the number entered is listed in the GAL and then pull in the associated name and location info.
The key is that I don't want this info to rely on the GAL going forward. For example, if an employee leaves or is no longer listed in the GAL, I don't want to lose the employee info (past data is needed for audit purposes). Note: I will be creating a report later to show if there are discrepancies between the GAL and my table, but that's another story...
So, what would be the best auto-fill options in Access 2007?
View 3 Replies
View Related
Apr 20, 2006
Not sure if this is possible but I have in my db a linked ODBC connection to a table. I use a Oracle 8 login to access the linked table. My question is can I capture the user login in ID somewhere in my table to show who is logged in? Thanks...
View 2 Replies
View Related
Nov 25, 2006
Hi Access World Community,
Im fairly new to vba and was wondering how I can refer to a username field and login field on a website that is within my Microsoft Web browser control.
I have some code below but it is giving me error91 Object vairable or With Block variable not set.
Private Sub Combo2_AfterUpdate()
Me.WebBrowser1.Navigate URL:=Me.Combo2.Text
Me.WebBrowser1.Document.Username.Value = "tester"
End Sub
It navigates to the site fine so it is narrowed down to {Me.WebBrowser1.Document.Username.Value} being the problem.
P.S. The website is https://www.taylorbeandirect.com/default.aspx
View 3 Replies
View Related
Jul 19, 2007
hi,
I linked a table (front to back) yesterday. I had a user enter information into this table. It's a login table. The login screen of the asp page is not able to find the record, even though I can see it in both tables. I am not sure what's wrong, since both tables show the same information. Does anybody have any ideas?
thanks in advance.
View 2 Replies
View Related
Apr 17, 2013
I have a database where multiple users log in and work in 1 of 2 different forms. The 1st form in for initial data entry where users enter data then senter there initials and save each record. the other form is for QC'ing data that has been entered and they will add additional data to the form, then enter their initials and save the record.
How can I make it so that I can have a user once they log in to the database, it will autofill their initials? I have used a dropdown box before but it is not useful for people who have the same first name initial or worst, the same first and second initial because they end up selecting the wrong initials.
I do not currently have it set up where I have a login screen, I just have only forms visible to users when they enter data. Any easiest way to go about making this happen (I am just trying to shorten the amount of data entry that gets done).
View 9 Replies
View Related
Feb 2, 2012
I have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase
and S:datadata.mdb is now residing in O:masterdata
and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so,
1. find unlinkable tables
2. search its new location under O:master
3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
View 5 Replies
View Related
Jun 17, 2013
I have access 2010, I currently have a database in place and operational. What I am wondering is there a way to have a form come up whenever someone opens access and asks for a password and login name. Also I would like to have a 2 tier system. One for Admins and the other for users with limited access.
Is this even possible to accomplish?
View 2 Replies
View Related
Jun 8, 2007
Hi All,
Although familiar with CR, SQL and other such things. I have never had cause to use Access all that much.... Until Now!
My problem is easiest explained by my requirements....
I have a db table called Source. The data for this Table is populated by a form called Technical. Technical containes a series of fields (SO No, SO Line No, WO No etc etc). Some of these fields are lookup fields linked directly to the SO_Header Table in our main ERP software via and ODBC connection. The result is the SO No field is populated with a list of Sales Order No's as they exist within the ERP software. Within the Technical Form I have also the Customer Name.
This is where the problems begin. Within the ERP software we have two tables which I need to use: SO_Header and Customer. The SO_Header contains all the Sales Order info and the Customer table contains all the customer info. What I need to do is populate the customer name field in by db with the relevant customer based on the SO No chossen. However the Customer Name does not exist in the SO_Header table. Instead both the SO_Header table and the Customer tables contain a field called Cust_Code.
Now if I wanted to link these in Crystal Reports I would link from the SO_Header table to the Customer table using the Cust_code field. This would give me the corresponding Customer name based on the Sales Order No.
How can I get the correct Customer Name to appear automatically in my Access form based on a SO No chossen by the user?
I would realy appreciate any help on this! If you need me to clarify anything please ask.
Thanks in advance!
RobV
View 1 Replies
View Related
Jul 24, 2014
I have developed a Microsoft Access 2010 database for my client and the database is split with Front-end/Back-end, the Back-end and the database is shared on Network, The client operating system and applications for all users are hosted and consistent and the service is delivered over Citrix.
The database some times corrupt the tables record and give a permanent #Delete Error, I have attached one of the database table and the screenshot of the error,
View 3 Replies
View Related
Jun 19, 2015
I have split database (B/E is in the SharePoint library, F/E has users on a local PCs). Sometimes, when I update/add data (does not matter if it is via form or directly in the table) it looks OK, but when I re-open the database, the data are gone.
Problem is that I cannot catch the moment when data were not saved (sometimes data are saved, sometimes not). I can point out this: if I re-enter the missing data, primary key continues subsequently, it looks like the data have never been entered. I tried to use script
Code:
If Me.Dirty Then Me.Dirty = False
on "On Close" form event, does not work.
B/E is linked by VBA code and it looks OK (no error, Link Manager shows correct path). I suspect interrupted connetion to the SharePoint but I don't know how to check it. I implemented VBA script co keep open connection to the SP but the issue persists.
View 9 Replies
View Related
Jul 22, 2015
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
View 2 Replies
View Related
Apr 30, 2015
How to Update ODBC linked tables on ms access?
View 5 Replies
View Related
Jul 16, 2013
I have linked tables in my db at the moment that rely on user dsn connections to an SQL server. I've been reading about DSNLess connections and want to try convert what i have to have permanent DSNless connections, but the code I've found doesn't appear to be working.
I've removed server specific details where i felt necessary, but when running the code i have it in place.
Code:
Public Sub RefreshODBCLinks()
Dim connString As String
Dim db As DAO.Database
Dim tb As DAO.TableDef
connString = "DRIVER=SQL Server;SERVER=<database ip address>;DATABASE=<
[Code] .....
View 3 Replies
View Related
Nov 11, 2014
I have a split database.(Access 2010).Three of the linked tables are Appointments, Appointments_OLD, Appointments_NEW.
I want to use vba to rename Appointments as Appointments_OLD (replacing the current one) and to rename Appointments_NEW as Appointments (replacing the current one)
I have used:
'replace Appointments_OLD by Appointments, replace Appointments by Appointments_NEW
DoCmd.Rename "Appointments_OLD", acTable, "Appointments"
DoCmd.CopyObject , "Appointments", acTable, , "Appointments_NEW"
Unfortunately this just made Appointments and Appointments_NEW clones of one another - changes in one automatically occur in the other.
What I actually want to do is to swap the names round in the backend database while maintaining the right links to the frontend.
Is this possible using vba in the frontend?
View 1 Replies
View Related
Dec 20, 2007
Hello All,
I currently have a bunch of tables that I have linked to a SQL database. For development purposes (easy of use), I'd like to turn all those tables into regular tables rather than linked so if I take the db offsite, all the data is with me. I realize there will be no updates, etc. but that really doesn't matter for my needs in this case.
Is there a script or an easy way to do this? (besides doing a manual import for a LOT of tables)
I've tried searching but I might not be searching for the correct terminology.
Thanks in advance!
Randy
View 3 Replies
View Related
Sep 19, 2013
looking for a way to export the list of table names, table types & if they are linked (e.g. tbl Sales Linked .dbf or tbl Staff linked to excel) from a database - this has to be done for about 300 databases.
in an individual db, I have a make table query off of the table MSysObjects to get the data. The Database field tells me where the source of the linked table resides & the ForeignName field gives me an idea of the format of the data source (e.g. dbf or excel). I could manually import that query into each db, run it to get the table names, then copy & paste..
View 3 Replies
View Related
Nov 28, 2014
In my Membership Database (Access 2010), I have a Table entitled [Foreigners] in which are stored the names of prospective members of the organisation and the name and ID of the Interest Group they are proposing to join.
When a formal Application to join is received their personal details are recorded using an Entry Form related to the main [Mail List] Table.
The current procedure then is to manually delete the entry from the [Foreigner] Table and finally to record their Interest Group data using another Entry Form entitled [GroupMembers]. This relies on an operator remembering to do the necessary.
It occurs to me that this process could be automated. I wondered whether it could be entirely automated or would be best effected with a button on the main Entry Form, such that on completion of data entry of personal details the button would be activated to do the necessary deletion and addition.
The [Foreigner] Table is an entirely stand-alone table, having no relationship with the other concerned tables. The only common factor will be the Member Name, which in the main table is a concatenation of First Name & Surname..
View 8 Replies
View Related
Nov 23, 2012
I have 2 tables tblworkdone and tbltests, both have a date field and are both subforms in a tabbed form on the main form. When I enter a date into the tbltests subform I would like the date to automatically be entered into the tblworkdone date field and create a new record so that when I move to the tblworkdone subform with the date already there.
View 5 Replies
View Related
Apr 13, 2007
Hi!
This is Kishore, working on VB Project which is using MS-Access95 as backend.
Now, i want to change the Database login Password.
Could anyone guide me in this context.
Regards,
Kishore
View 4 Replies
View Related
May 3, 2013
I have one database called asset management. It consists of one main table called cyber assets. Most fields in this table are linked to a manually created lookup table inorder to restrict user input. There are also two additional, none lookup, tables used to list a) the IP addresses (there can be more than one) and b) another similar 1 to many type table. Basically this DB is used to manage basic cyber asset data, excluding most items related to configuration management.
So, this above DB serves the purposes of asset management. Now I essentially need a similar DB for Patch Management. What I've done for this is to assess each patch initially (i.e. just by looking at the patch title and determining if we even have any of those device. i.e. this assessment is not based on OS, model number... just a general 'may' or 'may not' be applicable). Here's what this SEPARATE DB looked like:
Since each patch is essentially assessed against itself, or maybe a better way to describe it is against the users memory of what we do and don't have, only a single table and form was needed.
So now we've been thru this process and the DB is filled, all initial assessments are complete. The next step is to take all the ones that are applicable to our company (based on the initial assessment when you answer, yes is applicable) and do assessments based on each device we have.So what I want to do is to link the two DBs on a new table called Patches_by_device, inside the original patching DB... so the relationships would look like this:
But as you can see, the linked table CYBER_ASSETS has some sort of undefined relationship type, which is causing my issues.So the next thing I did was to autocreate a form based on the Patches_by_device table, and here's the result.I need to change the patch_key to the Patch_ID+Patch description+URL, etc, and to change the device key to the the UNID+IP+functional description, etc...so I changed the form record source like this:
Now I should be able to change the control source of the Patch_key and Device_key to more useful information. so I changed: Patch_key control source to Patch_ID and Device_key control source to UNID (which is in the cyber assets table)
As you can see, it worked for the patch_ID but not the UNID which is part of the linked table.Must it be within one DB, because we have a ton of other modules to implement (e.g. config management, vulnerability assessments, audit stuff, and more...) and I'd like all these to be in individual DBs, all liked back to the main cyber_assets/Asset management DB.I've considered just modifying that patch table so that each device has its own column heading in the table, but this will cause issues when new devices are added.
View 7 Replies
View Related
Oct 29, 2013
I am taking over a database someone else created but is no longer here. The tables are linked to an external oracle database. I know the links exist because when I run a query it prompts me for my user name and password. However, the tables look like regular tables. There is no icon showing it's a linked table so I don't know which tables are actually linked and pulling from the Oracle database.
View 6 Replies
View Related
May 6, 2005
I have a database with the tables linked from another database. The problem is that i need to copy the database to and from another machine in order to work on it. As this involves putting it in a different location each time, i have to relink the tables.
Is there a way to link the tables reletively so that as long as they are in the same Directory they will be linked?
View 7 Replies
View Related
