I'm a bit frustrated with how Jet is behaving with the server (MySQL).
Apparently it loves to issues commands like those:
48 QuerySELECT `city`.`ID` FROM `city` WHERE ( 'ABW' = `CountryCode` )
48 QuerySELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `city` WHERE ( 'ABW' = `CountryCode` ) limit 10
071114 13:44:25 48 QuerySET AUTOCOMMIT=0
48 QueryCOMMIT
48 QuerySET AUTOCOMMIT=1
48 QueryCALL BEGINTRAN
48 QuerySELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,` IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GN POld`,`LocalName`,`GovernmentForm`,`HeadOfState`,` Capital`,`Code2` FROM `country` WHERE `Code` = 'ABW'
071114 13:44:27 48 QuerySET AUTOCOMMIT=0
48 QueryUPDATE `country` SET `Name`='Aruba' WHERE `Code` = 'ABW' AND `Name` = 'Arubz' AND `Continent` = 'North America' AND `Region` = 'Caribbean' AND `SurfaceArea` = 1.93000000000000000e+002 AND `IndepYear` IS NULL AND `Population` = 103000 AND `LifeExpectancy` = 7.84000015258789060e+001 AND `GNP` = 8.28000000000000000e+002 AND `GNPOld` = 7.93000000000000000e+002 AND `LocalName` = 'Aruba' AND `GovernmentForm` = 'Nonmetropolitan Territory of The Netherlands' AND `HeadOfState` = 'Beatrix' AND `Capital` = 129 AND `Code2` = 'AW'
48 QueryCOMMIT
48 QuerySET AUTOCOMMIT=1
071114 13:44:28 48 QuerySET AUTOCOMMIT=0
48 QueryCOMMIT
48 QuerySET AUTOCOMMIT=1
48 QueryCALL ROLLBACKTRAN
I want to use transactions, but with Jet's behavior, it's useless because Jet insists on committing every update it does.
Disabling transactions in the ODBC driver didn't stop it. (This is why you see "CALL [Transaction method] in the query- I figured that if I made a stored procedure, disabling transactions, Jet would STFU and let me decide when to commit or rollback the transaction, but it blithely ignores the setting...)
There's no "Safe Transactions" value anywhere, and I don't know where I'd look to alter Jet's behavior, already having looked at regedit and reading the whitepaper on JET and ODBC.
I also seems can't to use ODBCDirect workspace for bound forms, and even if I set the recordset to a ODBCDirect workspace, I lose all Jet's intelligence in selecting only needed records (in which it does a wonderful job, I'd think). I also can see that if I create a ODBCDirect workspace, I would be creating one more (unwanted) connection to the server, which indicates to me that Jet/Default workspace will connect to the server anyway, and because the default workspace isn't an ODBCDirect, I can't modify/view connection properties.
Can I please have my cake and eat it? Anybody knows whether the behavior can be altered?
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....
Connecting Access FE to SQL SERVER BE Connection is fine. I can open and close it and other queries work fine. I have only one problem with the SQL in one query
Code: With rstRPT If FirstRecord = True Then .Open "SELECT Min([" & SourceTbl & "].[3Order ID]) AS MinOf3OrderID " & _
[Code].....
why it wants to see the variable as a column name?
I have a subform on a form I am using to track/display events. The subform is where the user enters in the names of team members. This number can vary from 0-12 and I currently have the subform grow to display all of the team members. However, it is all in one column and I cannot seem to figure out how to change the font size in the subform (I have tried multiple methods). What I would like to do is have a smaller font size and display the team members in a manner where no more than 4 names are in a column. If 4 team members are exceeded, I would like to see this in a column right next to the first column of team members. I hope I made sense and I hope someone can give me some insight.
Hi all, can anyone tell me what I'm doing wrong? I'm testing the code below that's supposed to enable all commandbars and make them visible if appropriate- but it is causing access to shutdown whenever I run it. If I can get it to work I want to be able to reset a users toolbars after my app has changed them-not perfect, I think this still dumps any customization the user had previously.
Public Function enableTools() On Error GoTo Err_enableTools 'enable all toolbars Dim i As Integer For i = 1 To CommandBars.Count CommandBars(i).Enabled = True Debug.Print CommandBars(i).Name DoCmd.ShowToolbar CommandBars(i).Name, acToolbarWhereApprop Next i Exit_enableTools: Exit Function Err_enableTools: MsgBox Err.Number & " - " & Err.Description Resume Exit_gottaGo End Function
I'm new to Access but have made a database of my music collection. Is it possible to get the selected mp3 file to play on the click of a button directly from a form? My music collection is store in several sub directories on my hard drive.
OK, My main table has an autonumber field in it to make records unique.
At the moment, the number 5-34 are all in use on active records. But when I create a new record either in the front end form, or the back end directly in the table, it attempts to start the record off with an already existing number.
No idea why this is doing this at all.
Is it possible to reset the autonumber to say 50 so all new records start at 50, then 51 etc.
I really dont want to lose the numbers already assigned, as there are other tables that rely on that autonumber as the clients ID to record all the other data.
Its probably something very simple, but I cant carry on with my data entry at the moment.
I have a switchboard on which I have items which when clicked should bring up forms or other switchboards but do nothing. They just play dead. They did work in the past (i.e in the last hour) but don't anymore. I haven't edited anything on the switchboards or the forms beng called so am a touch puzzled
Hey. My database would like, to make itself feel complete, a rele snazzy opening form. I have flash 8 and I've created a 1024x768 movie with some simple animation on it. I've managed to add the movie to the database as a ActiveX flash object and the animations play. The problem is making the object larger! It's staying very small no matter how I change the properties of the control. Any help would be much appreciated. For the sake of my databases mental health... of course. :cool:
To attach the movie I did so: http://www.databasedev.co.uk/flash_movie.html
Beginner using 2010. I wish to play MP3 files from a command button on a form. I would like to avoid third party / ActiveX controls if possible.
I am aware that use of "CurrentProject.Path" will be useful to locate the MP3 file within a directory within the Project Folder.
But have be baffled by the sparse previous postings about this subject (going back to 2004). Searching this forum yields zero results on the term "MP3".
This accdb/e will be used to present historical information to a small number of people and will be used with runtime without user input.
And all I need is Play / Pause / Stop. No other functions required.
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"
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
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
