ODBC Query Question
May 23, 2006
Hello, I have a question about querying some tables that i have linked through ODBC. I searched through the forum, but was unable to locate anything that helped me, please excuse me if i missed something.
I am trying to create an Access front end for user to query our AS400 through ODBC. I have a form setup for users to select a date range with my query pointing to the two text boxes on the form. This part works ok.
I would also like them to be able to pull specific codes, so i did the same as with the dates, and put a text box on my form and pointed the query to the text box. When running anything in this way, the query returns zero records.
For example, if I put any of the following in the query criteria, it will work and return records:
"MI"
Like "MI"
Like [Enter Code Here] (I then type MI when prompted)
however, if i put this in, it will return zero records.
Like [forms]![form1]![text33]
Yes, all the field names and form names are correct, i am keeping it simple for testing right now.
And even stranger, if I leave the LIKE off the above criteria, i get an ODBC error that says "ODBC -- Field would be to long; Data Truncated"
I in Text33 on my form, i have tried both "MI" and MI (without quotes), but they both return zero records.
Any idea what could be going wrong here?
Thanks in advance,
View Replies
ADVERTISEMENT
Jun 7, 2006
Hello all,
Need some help with someone who knows what they are doing (not me).
I have created a statistical analysis db in Access. Unfortunately the raw data I need is on the server in a different system. I understand I can run a pass through query to get that info and put it in my tables but.....
This apparantley requires connecting to the foreign database using ODBC something or other. I have the string, I just don't know how to write the code in VBA to do it so I can run my queries. What kind of code do I have to include using VBA to connect to the foreign database so I can run my pass through queries?
ODBC Connection String (I think thats what i need, right?) is below......
ODBC;DSN=spillman;HOST=10.0.40.20;UID=wjarre;PWD=* *****;
DATABASE=spillman;SERVICE=sqlnw
How do I write/add this in to my code?
confused
View 1 Replies
View Related
Oct 23, 2007
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
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
Feb 2, 2012
I have an access database with ODBC linked tables with a query. This ODBC connection is only available on my LAN because it uses specific ODBC engine.How can I publish the output of this query to a hosting space that my developer can query for a website project?The data needs to be published once a day.
View 1 Replies
View Related
May 12, 2005
I have a query that uses an odbc table.
The query prompt for a date: [Enter Date]
After entering the date, I get error: "ODBC Call Failed"
Yet...when I set the criteria to the date I want: #01/01/2005# I can retrieve the data, no problem.
Any idea why I get the ODBC Call Faile error when prompting for the date?
Thanks....
BJS :confused:
View 1 Replies
View Related
Nov 6, 2007
I have a problem with my query which works fine prior to upsizing to SQL Server. Now I have an Access Front-end with SQL Server (2005) back-end client/server setup. This problem has bugged me for over one full day and I can't find anything that seems to solve it.
The error that comes up is this:
Run-time error '3146':
ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '='. (#102)
The line that's producing the error is a DoCmd.RunSQL "SELECT tblPBC..." The full statement is below. I can't seem to see anything wrong w/ my syntax below....
View 5 Replies
View Related
Oct 26, 2004
Hi! I have a query setup that feeds off of three combo boxes on a form. It is setup to allow a user to search and calculate revenue for a selected time period by company, then department, then page. If all three or just company are selected, there are no issues running the data. However, if only company and department is selected I receive an 'ODBC--Call Failed' message. The issues seems to be stemming from the department field as I can link the department table to the main table and the query will run without error. The problem with that bandaid is that the query takes more than 15 minutes to run that way as opposed to about 2 minutes to pull more data at the company level.
Any suggestions on why this is happening or a fix that can be put in place? Any help is greatly appreciated.
I can provide more details if needed. Thanks!
View 6 Replies
View Related
Aug 7, 2013
When running a query in Access 2013 or 2010 we get an ODBC call failed. However when we run just the form, which the query connects to, it works just fine.
View 3 Replies
View Related
Oct 26, 2011
I have oracle ODBC Dirver which easily connect and fetch data to a new excel sheet according to the query.
But I want to fetch data to a access table from a remote server database which will automatically update the data, through the query saved.
Excel is working good with automatic update option but can't fetch data to access table.
View 2 Replies
View Related
Jun 11, 2007
We are using Access 2000 as our front end and SQL 2000 as the back end. We use ODBC to connect to the SQL database. One of my access 2000 programs was getting a little sketchy. So I created and new access 2000 database and imported all the modules, macros, reports, queries and tables. That all worked just fine accept now every time I open the new database it ask me to log in, I think it’s the ODBC connection. I have run table link manager, but it still asked me to log in. I have six other Access databases and none of them ask me to log in. So what am I missing?
View 1 Replies
View Related
Dec 1, 2005
Hi,
Requirement - To generate a report from a remote database
Methods opted -
1. Excel
- Connect remote database via odbc connectivity for the required query
- Excel generates the report and it can be refreshed when required
2. Access
- Connect remote database via odbc connectivity using link tables
- Generate report by writing a query on the linked table
- Since it is linked, it gets refreshed automatically
I want to go for ms access option since database is increasing day by day and excel will not be hold it.
But linking tables takes more time. Can you please suggest to extract data from remote database without linking tables?
It is not required to link the database in real time ie., No automatic refresh of data is required. Periodic refresh is enough.
Not the whole table is required to be linked. Query which returns current year data from the database is enough to generate the report. So, is there anyway to link query in ms access? Please let me know how to connect remote database fastly?
View 1 Replies
View Related
Feb 9, 2006
i am using access as a front end to SQL Server 2005 and link tables via ODBC.
Today when I run a query (normally works fine) I receive an ODBC Failed message box. I have tried opening each link table connected to the query and can open fine.
can anyone suggest other reasonf or this failure?
View 2 Replies
View Related
Mar 8, 2006
Hi
I'm using Microsoft Query to get data from a MS Access database into MS Excel. It seems that whenever I pull data from a UNION QUERY or SELECT QUERY based on a UNION QUERY I get an error message:
"Too few parameters...expected 8"
Can anyone offer suggestions?
Thanks
View 1 Replies
View Related
Mar 17, 2006
I am running a report that is built up of several backend queries.
I have identified the query that is causing the ODBC Failed message to appear, but cannot identify what is causing the problem.
I have tried to recreate the queries starting form the base query (which works fine).
Cannot anyone suggest anything?
Thanks
Paul
View 1 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 8, 2006
I 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 Related
Oct 19, 2006
I have a user that can only set up an Access ODBC driver using one listed as:
"Driver do Microsoft Access (*.mdb)"
They used to be able to set one up with listed as:
"Microsoft Access Driver (*.mdb)"
Any ideas as what the difference is and why it may have started preventing them from using the latter?
Thanks,
View 3 Replies
View Related
Mar 16, 2007
Good Morning,
Im trying to set an ODBC connection to automate the connection process.
The current code I have is:
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
MsgBox "Opening Connection...."
Set MainConn = wrkODBC.OpenConnection("Connection1", dbDriverPrompt, True, "ODBC;DATABASE=live; DSN=live;")
which works fine in bringing up a dialog box to choose the connection.
But what do i write in the database and dsn fields to enable the automation process.
See attached picture for the log in settings. All I have to do is press 'Connect' to start talking to the ODBC.
any help would be greatly appreciated.
Justin
View 6 Replies
View Related
Mar 21, 2007
Just a quick (hopefully easy) question.
Does anyone know if it is possible to link to Filemaker Pro tables for Mac, into Access?
The Filemaker DB will hold the data, with Access displaying the forms, along with some additional tables.
Does anyone have any ideas?
View 2 Replies
View Related
Jul 10, 2007
I use dsn file as below from Access to link to SQL server via ODBC.
From Access, I use this file but list all tables including system tables. How to restrict system objecs?
[ODBC]
DRIVER=SQL Server
UID=aspfun
Trusted_Connection=Yes
DATABASE=credent
WSID=aspfun_ws
APP=Microsoft Data Access Components
SERVER=order
View 1 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
Feb 14, 2008
My program is an Access 2003 database that connects to SQL 2000. I have a continuous form that shows between 10 an 25 records. I have coded an update button that when clicked updates a check box on each record displayed and then updates the form. Normally this works perfectly. However, sometimes when the button is clicked nothing happens for aproximately a minute then the user gets the error ODBC - call failed and no records are uodated. I have tried resetting the view from Dynaset to Dynaset (Incosistent Updates) and extending the timeout to 300. I have also tried extending the MSAceess Page timeout setting to 5000 instead of 5 under ODBC. Nothing has helped. Does any have any other solutions? Or a way to get a more specific error. I am really just guessing that it is a time out issue.
View 3 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 7, 2008
I wonder if anyone had any suggestions regarding how to link 4 ODBC tables so the info in each of the 4 ODBC tables are in one table. The 4 ODBC tables are linked in from a mapping system. Each table has 80% the same fields with some minor differences. i dont know if a union query would be the best answer in this case as the field in each of the ODBC's tables do not exactly match. Some have 12 fields, some have 14 fields. Any help would be appreciated?
also this would be using Access 2003 if you would like to know. thanks
View 1 Replies
View Related
