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......
I have a problem. I have a Pass Through query that I have saved (qryPassThrough) and I am using some VBA code to update the SQL statement in the query, like such:
Dim qdf as QueryDef set qdf = CurrentDb.Querydefs("qryPassThrough") qdf.SQL = "Insert SQL here..."
The error occurs on the "qdf.SQL = " line. The error is Error 3035: System Resouces Exceeded.
The error seems to occur only when the SQL portion of the query gets too big. If I start with a blank query, I can use the above code to modify the SQL with no problems. However, once I want to reset the SQL to a different statement, I get the above error message, but only if the SQL that is currently in the query is too long.
I have the following query that has been developed for me but it constantly asks for the password. Is there a way of storing/automating this for reporting?
Code: SELECT dbo.tbl_staff.Name, dbo.tbl_function.funct, dbo.tbl_dates.CalendarDate, dbo.tbl_activity.Details FROM dbo.tbl_staff INNER JOIN dbo.tbl_function ON dbo.tbl_staff.Funct = dbo.tbl_function.id CROSS JOIN dbo.tbl_dates LEFT JOIN dbo.tbl_activity ON dbo.tbl_activity.idstaff = dbo.tbl_staff.id AND dbo.tbl_activity.startdate = dbo.tbl_dates.CalendarDate
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.
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?
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.
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.
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....
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.
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.
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?
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?
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.
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:
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?
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.
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
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.
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
'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"
