General :: MS Access Reading Data From ODBC Connection
Feb 10, 2015
I am in need of consultation for MS Access reading data from ODBC connection. I have SQL Server that has all the data for the project financials etc.
I need a database that will read only certain data from the tables, for example, I don't need to import all 500,000 lines from SQL through ODBC connection, I just want to bring certain data for a list of projects whichever are opened and load only that data in MS Access so the group then can add additional details for that project in a shared MS Access.
Right now, all I can do is connect to that database through ODBC and brings all the data which I don't need all as it increases the size of the database, but just a criteria to specify which data to bring, if that's possible to do.
View Replies
ADVERTISEMENT
Jul 14, 2015
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.
View 2 Replies
View Related
Aug 31, 2012
I have a Access front end connected to a MS SQL database using a ODBC connection which is working fine of course.
When I compile it as a runtime version and install it on the business computer I get an error with the ODBC connection (I can't remember exactly what pops up). On the business computer I have installed SQL Native Client and in the ODBC added a System DSN (which works).
View 12 Replies
View Related
Aug 27, 2013
I have split a few different MS Access databases before with the BE on a shared drive and the FE installed on all users' local machine. In this instance, I have three ODBC connections bringing in data to my tool from an outside database once a day. Is it faster to store the odbc connections on the FE or BE when I split them?
View 2 Replies
View Related
Nov 18, 2012
I have a back end 2002 DB. I would like to disallow access to this through odbc connection. Is there some way this can be achieved? Thie directory where it is located is unfortnately cannot be restricted as the front end mde also resides there.
View 1 Replies
View Related
Jun 13, 2012
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 ?
View 3 Replies
View Related
Jan 17, 2005
How do I make use of a SystemDSN Connection when opening a recordset in VBA, rather than the default User Connection.
Here's the code I use to create the recordset.... Pest is the name of the connection, created via the Control Panel.
rs.Open strRs, "Pest", adOpenDynamic, adLockPessimistic
So I can create the connection entry on users local machines, without needing them to log in to do it.
Thanks.
View 2 Replies
View Related
Jun 21, 2006
Hi Everybody!
I am creating an Access database that will be used by multiple users. This database will be on the network and will be the front end of an oracle database connected to it via ODBC. Do I have to set up the ODBC to oracle only on the machine that hold the database on the network or do I have to set up an ODBC on each user's machine?
Thank you for your help.
View 11 Replies
View Related
Aug 15, 2007
Hello...
Question, do you need one for your database and if so why? I have read what they are but I haven't found really why you would need it?
Don't know if this is the right place to post this question but wanted to get some input on this.
Thanks
R~
View 5 Replies
View Related
Apr 24, 2006
Hi i would like to make a question about ODBC i have created an access database that connects via ODBC to an SQL server database, with the help of someone else i have created this code that works well for the link of 1 table that i choose to link the problem is that i want to link all tables from database and not doing it 1 by 1.
Is there a way to link all tables from database ???
The code i'm using is :
Dim strConnect As String
Dim strDatabase As String
Dim strTableName As String
strDatabase = DataBase_Now.Value
strTableName = "Tbl_A_Yles"
strConnect = "ODBC;Driver={SQL Server};Server=" & Server_Ip & ";DATABASE=" & strDatabase & ";UID=" & Server_User & ";PWD=" & Server_Pass & ";"
'Checks to see if table exists. If it does, delete it.
If TableExists(strTableName) Then DoCmd.DeleteObject acTable, strTableName
'And then use the trusty old TransferDatabase method to re-link the table declared in strTableName from the SQL Server
DoCmd.TransferDatabase acLink, "ODBC Database", strConnect, acTable, strTableName, "dbo_Tbl_A_Yles"
Thanks in advance.
View 1 Replies
View Related
Nov 27, 2006
I want a linked table in my current database. The server where the other access database is already has a ODBC connection created. How to I create a linked table to that other Database through the odbc? I know this is very unspecific, but I do not know what to do on either end. Thanks
T~
View 7 Replies
View Related
Jan 13, 2005
I want to do a odbc connection from acess and use it in my form. How would i accomplish this.Im using db2 database. is this possible?
THanks
View 1 Replies
View Related
Aug 11, 2005
I have been happily linking to a MySQL database via ODBC for a while but all of a sudden, Access 2003 is crashing whenever I try to see the actual MySQL database. No message given. It just gives the Microsoft Error window and closes down the MDB. ODBC tests succesfull. Any ideas?
View 14 Replies
View Related
Apr 5, 2006
I am an A2K novice and I need some help with connecting to an AS/400.
I have my ODBC data source created and it works well. I'm comfortable with the SQL syntax I'll need in A2K to fetch my AS/400 data. I have no idea what is necessary in the way of VB code in A2K to do the connection. Can somebody point me in the right direction where I can find some code samples? I searched this board prior to submitting this thread but came up empty. Time is ticking on this project.
All help is greatly appreciated.
View 14 Replies
View Related
Apr 19, 2006
Hi, I have a front end that i have developed in access which manipulates a table stored in an oracle database. I have an ODBC connection to the oracle table.
When i open the access front end i am confronted with the
Oracle ODBC Driver Connect and am promted for username, service name and password.
My question is... Can I in any way take what is inputted into the driver connect for username and password.
For example to deactivate/activate some command buttons depending on the username that is inputted
Can anything like this be done or is this driver connect a totally individual thing?
Thanks
View 1 Replies
View Related
Jun 12, 2006
Hello Guy,
We use an Oracle base software called Trapeze and Microsoft Access sometime used as a front hand to access the Trapeze oracle tables.
Due to the possible dangers of allowing the users Access, Is there a way to block the users access to Administrative Tools, to create ODBC connectivity to our databases? Trapeze's security is antiquated and uses Oracle to validate accessibility. Since a user has to be created in Oracle, with update, delete, read, write, append rights, for Trapeze to work correctly, a user can connect directly to the Db through Access and make changes directly to the data, unless the ability to prevent them from creating their own ODBC connections.".
Any ideas on how to prevent them to create that ODBC connection?Thank for your help.
View 9 Replies
View Related
Aug 22, 2006
I have a Acces 2000 front end that links to an Oracle 9i back end. Everytime i open a form/table the ODBC dialog box appears asking for username/password/server. Is there a way to bypass this using vba code so that when the Access app starts the connection is established behind the scenes and therefore preventing the dialog box appearing.
View 3 Replies
View Related
Aug 20, 2005
Hi all, I am using access as a FE for a mysql BE. I have the odbc mysql driver installed on the workstations. My issue is that I don't want the users to be able to use the driver in say excel.
I have the password saved in the odbc setup. So any application can use it. If I don't save the password in the odbc driver then access asks for the password when you open it.
Is there anyway to save that password in access (and still use linked tables), so the odbc connection can't be used else where?
Does this make sense?
Thanks,
Michael
View 1 Replies
View Related
Mar 9, 2007
Really don't know the answer to this question and I thought I'd come to the experts. I have created an Access database that I use to push & pull data from an SQL database through linked tables. I created an ODBC connection on my PC and set it so the links keep the username and password for the ODBC connection. All that works great...Now my question - Is it possible to somehow "store" the ODBC connection within the Access database so that I don't have to go to every PC that is going to be running this database and create an ODBC connection? The Access database and the SQL database both exist on our network and all those that will be using it have access to both. If someone has the answer I would really appreciate it.
Thanks
View 8 Replies
View Related
Feb 19, 2006
Glad I found this forum.
First question as user :)
I have a database I am creating with a number of subforms. A few of the subforms I want to be able to quere data from a ODBC connection. When I use the combobox to select a system, the subforms with ODBC connections are not updated. I will not be updating any of the data on the ODBC.
Should I just create a macro that does a delete quere and then make-table quere, or is there a better way?
thanks
brandon
View 1 Replies
View Related
Feb 9, 2015
I have created an Access database that stores a significant amount of information about our products. I have an excel spreadsheet that performs calculations on that data. I have used the import data from Access tool in Excel to import the data. The problem I have is when the excel spreadsheet is open no one can modify my Access database.
Is there a way to have Excel open and still be able to work on the database in Access? When the user on Excel needs updated information they can just hit the "refresh" button and everything up to that point that has been done in Access will update. I do not want Excel to be able to manipulate or change any values in my Access database.
I am using Access 2007....
View 5 Replies
View Related
May 23, 2007
Hi, I have created a File DSN using my ODBC under Admin Tools and placed it on the network and have linked my Access 2003 tables to point at this.
I have asked a non-domain user to log into this database and open a table but when they do, it defaults to a Domain account until you untick trusted Source. You then have to enter the username and password for the ODBC connection. I thought the whole point of a File DSN was to eradicate the process of logging in. Can anyone Help?? Thanks
This is what the ODBC looks like through text editing software.:eek:
[ODBC]
DRIVER=SQL Server
UID=MyUserAccountName
LANGUAGE=us_english
DATABASE=MyDatabaseName
WSID=MyWorkstationName
APP=Microsoft Data Access Components
SERVER=MyServerName
Description=MyDescription
View 2 Replies
View Related
Feb 9, 2008
I Need help,
I have an access 2002 front end and I am linked to a sql server express 2005 backend and in one of my tables I am getting this error when I add a record to a work order table. The error is ODBC connection on INSERT failed on linked table.
A few strange rhings happen. It only involves customers with over (approx.) 140 work orders (there is a one to many with customer to work order). All other customers seem to be immune to this.
Also, if you go to the tables directly from a access database window, you can still add/edit or delete records, but in the form view, it gives you an error.
I have tried several things including changing code (the orgional code was set dbs= currentDB and dbs.execute("INSERT.......)
I have found similiar posting but no real direct causes. does anyone have any ideas on how to resolve this?
Thanks
Frank
View 1 Replies
View Related
Feb 24, 2014
I made multiple pass through queries that receive their data via an ODBC link which is established. Currently the username and password is entered seperately in every query but I was wondering if it is possible (and how) to make both look for the values in a table (OR somewhere else if possible) for these values. The following code (with edited information) is used in all queries.
Code:
ODBC;DSN=DSNNAME;Driver=Firebird/InterBase(r) driver;Dbname=Database.FDB;CHARSET=NONE;PWD=Password;UID=User;
The reason that I would like to do this is to ensure that the user can change it easily.
View 3 Replies
View Related
Oct 28, 2014
I am using MS Acsess 2010 and some time I am getting ODBC Connection Error. How to increase the ODBC & Query Time Out property in MS Access?.I am using 32 bit version of Access 2010 on a 64 bit operating system.
View 1 Replies
View Related
Oct 17, 2004
OK, I give up.
I have just finished 4 hours searching and searching.
I have access to a MySQL database that has a time field. I wish to use the DTS packages in SQL Server 2000 to aquire the data to the SQL Server.
I can connect/see it using an ODBC connection, both with access 2k and the DTS bizzo on SQL server.
The time data is being seen by the microsoft end as a date format with way out values.
As MySQL has a very obvious different way of handling date/time (2 data types - one date and the other time), there must be an obvious solution to be able to use this data in access / SQL server.
Hiowever, I cant seem to find it.
damn - fire alarm must go - please help if you understand this - i will ammend later
mike
View 2 Replies
View Related
