Hello all, is there a way to change an ODBC connection in MS Access. I have 4 ODBC connections that I can choose from (all of them from the same server), and instead of manually recreating a new ODBC connection everytime, I would like to point it to the new connection. The ODBC connections (Tables) have the same information, but throughout different times during the month.
Thanks in advance for your help.
I have a query that I want use as a pass-through query (SQL Server databases).
The query has a sub-query that references another database. The query runs fine in Enterprise Manager because EM allows me to access both databases.
If I wanted to I could link both tables through ODBC and then run an Access query that would do the same, however, I would rather use a pass-through-query for this.
The problem is that a pass-through-query in MS Access only allows me ONE ODBC connection (as far as i can tell).
My question is:
Is there a way I can run a sub-query (referencing a different database. i.e. second ODBC connection) which references a second database?
Is this possible?
Thanks
-Gary
Hi!
I'll cut right down to it: I have an ODBC connection to a (SQL Server) test database on my development machine that is identical to the live database to which a (different) ODBC connection exists on the PC where this Access application is to be deployed. The problem is that when I deploy, there are some tables that have to be updates, because they still point to my test database. The other tables look like they use the default database for that user.
I used to be able to just copy the Access application and it would work without having to update any linked tables.
To clarify, the tables that work without a problem look like this in the Linked Table Manager:
t_mytable (DSN=odbc;)
And the problem tables have this:
t_mytable2 (DSN=odbc;DATABASE=my_test_db)
I don't seem to be able to clear that DATABASE= thing up. Can anybody tell me how this works?
If this question is overly trivial, I apologize.
Hello Guy,
We use an Oracle base software called Trapeze and Microsoft Access sometime used as a front end to access the Trapeze oracle tables.
Due to the possible dangers of allowing the users Access, Is there a way to block the users from changing the oracle tables and only be able in MS Access to create reports and queries? 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 modifying the tables and only create reports and queries that they can save.
server administrators have expressed concern about my website that it is having a negative impact on the server.
He said this typically occurs when a website has a memory leak (where it obtains server resources and doesn't release them) or is opening database connections and not closing them until the point where no more are available.
I'm using asp.net to control Access and open the connections.
I don't know why it isn't closing them? the website is:
pedalcar.com
Any thoughts of what the problem is and what the statement I need to close the connections?
Hi,
we use access for localisation in our application. The business object which accessess the access uses DAO connectivity and we often land to
"Too many client tasks" problem.
is it wise to change the DAO to ADO? if so the above problem will go off?
what are the chances that the problem still persists?
is that access has any limits on the number of connections? like 64, 255? or is that ADO/DAO has any limitaions on the number of concurrent connections to access DB?
Pls help, your help will be highly appreciated.
Thanks,
Venkat.
I have a database that tracks my client information. The forms also include the agent servicing the client and the referral source from where the client came from. I also have tables and forms for each referral source and agent. Currently, if a client is referred by a referral source I go to the referral source table and make a note of it in a text box. Instead, could I tie the referral source and client table together? If, on the client form, I select that a client "Tom" came from my referral source "john" could it tie the client to the referral and note it on the referral form? My referral box on the client form sources the table of my referrals.
View 6 Replies View RelatedI am having a problem with an access database that is connecting to a sql server. When trying to run any queries it is very slow. I think the problem might be caused by the ODBC link and I was wondering if there are any alternatives to using ODBC to connect access and sql.
View 5 Replies View RelatedI want to restrict the number of times each person can open a database, but give them the option to force close the database on another PC, if they have left it with the database still open, prior to allowing them to have full access / functionality of the database on another PC.
In order to achieve this, I need advice on how to automatically record the computername as each person "logs in" to the database and secondly, send a command to close the database / Access on a PC with the stored computername.
Thanks in anticipation.
I have split my database and have the front end on numerous PC's. Is this the preferred method of a multi user database? The reason I ask is not all the data is making to the backend - only some records.
View 1 Replies View RelatedUsed to use these forums quite regularly and always found people to be of help, even started chipping in occasionally with the odd pearl myself :rolleyes:
Anyhow, having forgotten my username i'm back under a different guise and have a problem that i'd like some help with.
My company has a website running asp (not .NET) with an sql database backend. Basically we want a localised system to deal with order processing with daily updates to customer, order tables etc. I started to build something in VB and SQL but time is critical and once I work out this problem I can churn something out in access double quick time.
I can get the ODBC connection open to the sql database and import the data to Access 2007 table (ie dbo_tblOrders), but I then need to find/write some code to fetch extra records daily and append them to the EXISTING tables. At present data is fetched and added to a new table. Is it merge queries I need? If so how, and can I set it to only fetch new records?
Hi ,
I need to link some pages in my site to Access do I need to create ODBC for the connection ....
Hi,
I want to import into an MDB table a csv file.
I'm trying to use the bulk copy table.
my function is:
SQL = "SELECT * INTO [my_table] FROM [ODBC;Driver=Micrsoft text driver (*.txt; *csv) ;Dbq=c:\;Extensions=asc,csv,tab,txt;].table.csv"
db.OpenEx( "Driver=Microsoft Access Driver .mdb);DBQ=c:\access.mdb;", CDatabase::noOdbcDialog );
db.ExecuteSQL( SQL );
when i run this function i get an error : "You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database"
when i try to import in the same way a dbf file (insted the csv file) with VFP it's working well.
what seems to be the problem? how can i fix it? or if some one know how can i import a large csv file into access DB in an efficient diffrent way?
thanks ishay
I finally got a chance to test the setup remotely, and it basically works with all the links I've set up.
I wanted to see how it would behaves should it disconnect or something and thus manage it gracefully.
First, I broke the network connection and tried to navigate to next record. Instead of getting a error message, Access just crashes. I'm not sure if this can even be trapped at all.
Secondly, I disconnected the connection then reconnected it. Access then tells me that the server has gone away, presuming because it's still looking to the old connection that was broken. Running the code to repair link fails also with same error. Can this be resolved with having to quit & restart Access?
Thirdly, I put a breakpoint on Form's Error Event but this doesn't seem to always to catch all errors. It does catch error if I tried to start up a form without the connection, but if it already has been started, and the connection was broken, the Error Event is never fired even though I get a dialog saying ODBC Call failed, then a zillion of dialogs saying Object no longer set or invalid. This would be extremely annoying to my end users.
Can any of those be avoided or fixed?
I've moved tons of text files or Excel files into Access DB's but I need to take an ODBC report and put it into a table on a new Access DB. If I save the report as a text file I get boxes when I import it to the new DB. If I use Excel I have to make a new table each time. Can anyone suggest the simplest way to take an ODBC report and put it into an Access table. THanks
View 1 Replies View RelatedI've just set up my Access on a new PC and now when I try to connect to my MySQL database through odbc, Access crashes without any error messages, just the standard microsoft error reporting message.
I'm using Office 2003. What do I do??
Hi m8
I have Microsoft Access 2003 which is running fine. I have setup ODBC source through windows xp Professional control panel.
While i am in access application and try to open by selecting
File/Get External Data/Link Tables
It open Link PopUp Window, then from the Files of type drop down, I select ODBC Database(). Windows just disppear, not opening odbc source selection
window.
Please could you help me, why this is happening and sort it out.
Many thank
Please help...
Database Results Error
[Microsoft][ODBC Microsoft Access Driver]
General error
Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xcac Thread 0xcc0 DBC 0x1090064 Jet'.
The code: example.asp
Code:<%fp_sQry="SELECT count(*) FROM TEVPDATA where ((datediff('d', '" & date() & "', DT3PROF1) between 0 and 30)) and (COL2 = 'PROF1/SLM')"fp_sDefault=""fp_sNoRecords="<tr><td colspan=1 align=left width=""100%"">No records returned.</td></tr>"fp_sDataConn="Fabos"fp_iMaxRecords=0fp_iCommandType=1fp_iPageSize=0fp_fTableFormat=Truefp_fMenuFormat=Falsefp_sMenuChoice=""fp_sMenuValue=""fp_iDisplayCols=1fp_fCustomQuery=TrueBOTID=1fp_iRegion=BOTID%>
Global.asa:
Code:'--Project Data ConnectionApplication("Fabos_ConnectionString") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=data/Fabos.mdb"FrontPage_UrlVars(4) = "Fabos_ConnectionString"Application("Fabos_ConnectionTimeout") = 15Application("Fabos_CommandTimeout") = 30Application("Fabos_CursorLocation") = 3Application("Fabos_RuntimeUserName") = ""Application("Fabos_RuntimePassword") = ""
I am working on an Access 2002 database where one of the tables has five required fields making up the key.
There is a form that is linked to this table which is used for adding new records.
There is a close button on this form that has the following code in it's OnClose event:
"DoCmd.close"
When the form is opened, no data entered and then the close button is clicked - nothing happens.
It used to work fine, until I recently upsized the database into a SQL Server database and now it's all gone horribly wrong.
If I close the form manually by clicking on the cross, I get this
error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value
NULL into column 'Product', table
'LeasebookSQL.leasebook_user.leases'; column does not allow nulls. INSERT fails. (#515) [Microsoft][ODBC SQL Server Driver][SQL Server] The statement has been terminated. (#3621)
Can anyone please tell me how I can get the form to close without trying to save the record, especially when no changes have been made to the data on the form?
Thanks,
Simon
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).
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.
I have split my database in Front End och Back End, and both are password protected. The Front End has been converted to ACCDE.
When I try to import to Excel from Front End and/or Back End using database connections I receive a message telling me that I am using an invalid password (even though I use the same password when I encrypt/decrypt FE and BE). I've tried using Access import, ODBC and Microsoft Query. (In some cases I 'm also prompted to set a username, which I do not know which it should be since I haven't set one in my database (except the automatic "admin"))
How do I go about to make this work?
I am trying to create an ODBC link to a copy of the Northwind data base.I am running Win7 on a 64 bit operating system but am keeping office running as 32 bit for Access, Excel , Visio etc due to the fact that I have a lot of old apps tied to them.
When I attempted initially to create a new system DSN ODBC connection it only allowed me to use SQL drivers. (No plurry good) So after some searching I found out that I could use a file in C:WindowsSysWOW64 called "odbcad32.exe" which has allowed me to view /use the total list of drivers to import data.So I can set up a DNS ODBC named Northwind1 and I can access the database without problems using Excel.However if I try to use the same ODBC link in Access to connect to Northwind1 I get the following error message:"You cannot use ODBC to import from export to, or link an external ?Microsoft Access or ISAM database table to your database".
My main experience is with MySQL and PHP so I'm kindof stumbling around in the dark here. Heres my story:
We recieved a new server and migrating from (Windows Server 2000, IIS 5.0, Access 2000) to (Windows Server 2003, IIS 6.0, Access 2003) has been nothing but problems.
I have setup IIS to work with ASP, I've also setup a system DSN. The access file was copied over from the previous server. We're running Access 2003 and now I can't seem to get this ASP script to work.
When I try to upload a file to the script I get this error:
Line: 42
Char: 7
Error: HELP!!! Error: [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement expected 'DELETE','INSERT','PROCEDURE','SELECT', OR 'UPDATE'.
Code: 0
Url: (URL address blocked: See forum rules)
Here is the part of Connect.asp that errors:
Code:<script LANGUAGE="javascript"><!--var DSNString, TheDF, TheRS;//DSNString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C: hedata estdisbo.mdb;User Id=admin;Password=;";//DSNString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C: hedata estdisbo.mdb;Uid=Admin;Pw d=;"DSNString = "DSN=testweb; UID=; PWD=";function public_ConToDB(){ TheDF = DP.CreateObject("RDSServer.DataFactory",this.text1.value);DC.DataSpace=DP;DC.Server = this.text1.value;DC.Connect = DSNString;//DC.Connect="d: hedatadisboweb.udl";}function public_GetRS(SQLCommand){ TheRS = TheDF.Query(DSNString,SQLCommand);DC.SourceRecordset=TheRS;return (TheRS);}function public_GetDC(){ return (DC);}//--></script>
Its included in a file called FileRead.htm here is the part that refers to the error lines:
Code: var TheFile, FileStrm;// Retrieved field namesvar M_ssn, M_inits, M_Lname, M_Fname, M_DSSN, M_ruc, M_paycode;var M_normamt, M_payamt, M_pgrp, M_poe, M_pltcd, Payrollnum, Payrolldate;// var Trash, FieldData, FieldNames;var TrashLineNum = 1;// Create the file Stream Object (ReadOnly)TheFile = new ActiveXObject("Scripting.FileSystemObject");FileStrm = TheFile.OpenTextFile(FName,1,false);// Setup the Database Connectionvar SQLCommand, TheRS, TheConnection, DSNString, Commands, MyDC;FieldNames = new Array("ssn","inits","lname","fname","dssn","ruc","cco", "payamt","normamt","notsureone","pgrp","poe", "pltcode","recstatus","paychgflg","paystatuscode", "payrollnum","payrolldate");// Connect the the DatabaseDataLib.ConToDB();MyDC = DataLib.GetDC();// Get Payroll NumberPString = prompt("Enter Payroll Number","025");Payrollnum = parseInt(PString,10);// Get Payroll DateDString = prompt("Enter Payroll Date","20000101");Payrolldate = DateLib.FromMil(DString);// Setup the Database Connectionvar NumRecs = 0;//MyTest = DataLib.GetRS("PaydayUpdate1");alert("About to cross into the CurExtract!!!");MyRS = DataLib.GetRS("SELECT * FROM CurExtract");
I set an alert after this line but it doesn't pop up.
ANY HELP WOULD BE VERY GREATLY APPRECIATED. I'll be checking this very frequently.
Sincerly,
Joseph Russell
How to Update ODBC linked tables on ms access?
View 5 Replies View RelatedHello, this is my first post (anywhere ever)!:eek:
While I have lots of Access programming experience over the years I have broken new ground this week and am in unchartered territory and need help please!
In the organization I am working for (as Procurement Manger), we use AMMS (by Microwest) for work order generation and inventory management, etc. I have been able to import into Access the tables of interest from this SQL-based package. The problem is that I need to share the tables (via nice front end forms/queries) with about 20-30 people in the field.
I don't know much about ODBC but the administrator of AMMS was kind enough to give me access to the AMMS database tables via ODBC and I, personally have access to read the table data but none of the other people in the field can access these tables due to security rights.
So, what I have done is I created MS Access tables for each of the 4 AMMS tables of interest and I run code that runs delete and append queries on each table. I then have a separate public db that the field people access with all the queries and forms but only links to the Master db containing the 4 Access tables derived from the ODBC tables. This works ok but there are issues:
1) if any field people are on the Public db, the code to import the ODBC table data loops and does not complete the delete/append cycle on one or more of the tables. I can only refresh the table data when nobody is connected to the db - impractical.:(
2) the ODBC tables are rather large - one is over 38,000 records and it takes several minutes to run the delete/append queries.:mad:
3) When I created the Master db it was some 83MB. After running the delete/append queries it is some 179MB and appears to increase in size every time I run the delete/append code. When I run "compact db" it stops running after a short time. Does nothing but create a new file (db1.mdb) of the same huge size as the Master.:confused:
I suspect the most graceful solution would be to give each user rights to the ODBC tables from AMMS in Access. In this case, I wouldn't have to run delete/append queries and the data would always be in relative real time subject to the refresh timer. Unfortunately, due to AMMS licensing restrictions, this is not doable.
I have tried to search the internet for guidance but no luck. This forum appears to be an excellent opportunity to get expert advice on this particular issue. I thank you for your thoughts and guidance.