Updating MS-Access Database query
hello i need to update my MS-Access database thru matlab. when using the following code using function "updates" it updates the database i.e .......whereClause = 'where month = ''Nov'''..... Here "Nov" is an entry in the database. Now when i assign a variable to the months..ie variable name "MNTH" could contain Jan /Feb etc,.....and i want to pass the variable "MNTH" ie .......whereClause = 'where month = ''MNTH'''..... it DOES NOT update the database and i receive the error .....error in horxcat....cannot convert from character to cell array.... Why am i not being able to pass a variable name which contains the field name to be updated in the database? Kindly help Thanks Ashish
ashpant
comp.soft-sys.matlab
211264 articles. 
25 followers.
lunamoonmoon
0 Replies
1622 Views
Similar Artilces:
MATLAB command to access MS Access database
Hi! Is there a MATLAB command which gives access to a .mdb MS Access database or do I need a special driver? Thanks, Andree Hi Andree Ellert, you can use activeXcontrol to interact with MS-Access database. access = actxserver('Access.Application'); returns the handle for Access. set(access, 'Visible', 1); will show the opened Access window. use get(access) and set(access) commands to find the methods available to call. -Vadivelu M =========== http://www.mathworks.com/access/helpdesk/help/toolbox/database/datatool.shtml "Andree Ellert" <ellert@gmx.net/////\\\\\> wrote in message news:eed9874.-1@webx.raydaftYaTP... > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree Andree Ellert wrote: > > > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree <http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=4045&objectType=file> -Hardik ...
Use ASP to read MS Access query that calls MS Access function
Hi: I have an MS Access query for a table called MyTable. One of my fields in the query is a custom field that looks like "MyField: = MyFunction(ID)" where ID is a field in MyTable. I have ASP code and I can use it to read the query OK when "MyField" is not part of the query. If I add in MyField and then run my ASP code to try to read all the query fields, I get an error message on my web browser which says something to the effect of "cannot find MyFunction." On the Access side, I have MyFunction set up in a module and use the keyword "Public" in front of the function name. What else do I need to do to get my ASP code to run this query. I'm trying to avoid reproducing MyFunction within the ASP code if possible. Thanks. Kevin On Thu, 14 Jun 2007 21:38:19 -0000, k-man <kmccrack@gmail.com> wrote: Sorry, no can do. From ASP you don't have access to the Access VBA interpreter. -Tom. >Hi: > >I have an MS Access query for a table called MyTable. One of my >fields in the query is a custom field that looks like "MyField: = >MyFunction(ID)" where ID is a field in MyTable. > >I have ASP code and I can use it to read the query OK when "MyField" >is not part of the query. If I add in MyField and then run my ASP >code to try to read all the query fields, I get an error message on my >web browser which says something to the effect of "cannot find >MyFunction."...
MS Access, Oracle 9i, security, and pass-thru update queries
Architecture: Access 2003 client, Oracle 9i repository, no Access security in place, ODBC linked tables. 100 or so users, in 3 or 4 groups (Oracle roles actually): Admins, Updaters and ReadOnly. Each group sees a different set of menu options when they open the client and login to Oracle. For the sake of speed I use pass-through queries here and there for updates and deletes. I update their SQL property in code and execute them. Since the pass-thru queries need Admin priveleges, I could/would like to store the Admin password with the query. But that's Security Breach #1, since you can import the query into another .mdb (even from the .mde), and see the Admin password. To get around the breach, I don't store the password with the query, and when Admins login I create a pass-thru query in code, and connect to the db with the Admin password, thus setting their permissions for the session. This works fine, but it's kind of kludgey. I'd like to protect the queries (and ODBC table links) from prying eyes, and I definitely won't be using native Access security. Ideas? Thanks DFS wrote: > Architecture: Access 2003 client, Oracle 9i repository, no Access security > in place, ODBC linked tables. > > 100 or so users, in 3 or 4 groups (Oracle roles actually): Admins, Updaters > and ReadOnly. Each group sees a different set of menu options when they > open the client and login to Oracle. > > For the sake of speed I use pass-thr...
newbie needs guidance from mastuh on querying mysql database from ms access... please
i've already set up mysql server and msaccess wif myodbc driver (myodbc 3.51) on my computer and i've created a new "database" (it only links to the tables in mysql server) in ms access. The problem comes when i try to do a UNION query from ms access on 3 or more linked mysql tables, it prints me an error... any ideas? It works when i do a union query only on 2 tables but not on more. i've checked that the number of fields in the tables and the field properties of each table are the the same, but i still get that error. thanx for your time "angel duran" <angelduran2004@hotmail.com> wrote in message news:a4e32a26.0408181126.5ee648cc@posting.google.com... > i've already set up mysql server and msaccess wif myodbc driver > (myodbc 3.51) on my computer and i've created a new "database" (it > only links to the tables in mysql server) in ms access. The problem > comes when i try to do a UNION query from ms access on 3 or more > linked mysql tables, it prints me an error... any ideas? It works when > i do a union query only on 2 tables but not on more. i've checked > that the number of fields in the tables and the field properties of > each table are the the same, but i still get that error. thanx for > your time MySQL Server = You get what you pay for. MyODBC Driver = You get what you pay for. Find out what "free" is all about at: http://www.mysql.com/support/techsupport.ht...
Excel database query does not work after securing an Access database
Hi all, I'd appreciate if someone could give me a hint to this little annoying problem: I have a simple database that I want to be able to query from Excel. When I use the "new database query" wizard on an unsecured version of my database, everything works fine. I am able to read the tables and queries, I can build customized queries in Excel from the Access tables/queries. Now, the moment I secure my database I loose all connectiviity between Excel and Access. The wizard won't even connect to the database. When I add permission to "open/read database" object through "Users and Groups Permission" in Access, I am able to connect to the database and see all the tables/queries, but it does not show the fields. I thought that in theory if I add permissions to "read design" and "read data" to my tables and queries for the regular users that should solve the problem, but it doesn't. I am obviously missing something somewhere, but what? I searched the web but could not find anyting. I am using Windows XP Pro SP2 and Access 2003 and Excel 2003. Any advice???? Michael I'm actually just facing this problem as well myself, and have no answer. I have a secure database that's split front and backend. I've had split secure databases before where I have been able to query via excel, but for some reason, this one won't let me. ...
access MS-Access database in C# Windows Application
Hi, I am trying to access the MS Access database on my local machine in C# Windows Application in Visual Studio.NET. But it seems that it does not work. I use the Microsoft Application Blocks to access the database. I have tried 2 possible connection strings. ----------------------------------------- str_1 = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + DBFilePath; str_2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBFilePath; //SqlHelper is the class in the Microsoft Application Blocks SqlHelper.ExecuteNonQuery( str_1, CommandType.Text, SQL_str); ----------------------------------- The error message I got is: "Unknown connection option in connection string: driver." for str_1 and " Unknown connection option in connection string: provider." for str_2 So I am wondering, where is the problem? Is it because my connection string for MS Access is specified wrongly? Or is it because I cannot use SqlHelper to access the MS Access database? ( I have used SqlHelper class to access the MS SQL server, and it works) Your help will be greatly appreciated. James James wrote: > Hi, I am trying to access the MS Access database on my local machine > in C# Windows Application in Visual Studio.NET. > > But it seems that it does not work. I use the Microsoft Application > Blocks to access the database. > > I have tried 2 possible connection strings. > > ----------------------------------------- > ...
How to reset or disconnect all conections to an MS Access database if the database is hung up?
Hi, All: Sometime I do not knwo why my database in local C drive is locked. If I restart the LabVIEW software, it works fine. Is there a way to disconnect all connections before you make a new connection? I remember I used this function a couple of years ago but I could not find the function in new LabVIEW database connectivity tool kit. I am using LabVIEW 8.0.1. Thanks. Jim Is it DB Close Connection? You VI should contain one of these. <img src="http://forums.ni.com/attachments/ni/170/194552/1/DB%20Close%20Connection.jpg"> DavidMessage Edited by David Crawford on 07-11-2006 07:38 AM DB Close Connection.jpg: http://forums.ni.com/attachments/ni/170/194552/1/DB Close Connection.jpg David: Thanks for your posting. I?did use the functions you mentioned. I?want to know if you can disconnect all database connections if you somehow forget to disconnect. Anyway, my problem has been solved. The reason is the data source name missing after some operation. Thanks again. Jim ...
Accessing a passworded MS Access 2000 database in Crystal Reports
Can anybody give me some code that I can put in to my VB6 app to open a passworded Access database in the Crystal Reports 8.5 RDC? I see so many examples - none of which seem to work for me. I am using a native connection in Crystal ( pdbdao.dll). My current code is : Dim Report As New crPrintPreview Dim crTable As CRAXDRT.DatabaseTable Private Sub Form_Load() Set crTable = Report.Database.Tables.Item(1) varpath = CurDir crTable.Location = varpath & "\OrderManager.mdb" CRViewer1.ReportSource = Report CRViewer1.ViewReport End Sub All I receive is the message "Error opening file xxxxxxx etc" and then "Open Database session failed" Appreciate any help! Peter Tyler ...
update oracle database when ever changes are made in access database
Hi all, well i have migrated the database from access to oracle and changes would be done in access only so i want to make same changes in the oracle database say after 10 mins. or so everytime i.e a continuous process either changes made or not so what should i do. "Varun" <varuns123@ggn.hcltech.com> wrote in message news:a76d8c39.0407272029.5df9d058@posting.google.com... > Hi all, > well i have migrated the database from access to oracle and changes > would be done in access only so i want to make same changes in the > oracle database say after 10 mins. or so everytime i.e a continuous > process either changes made or not so what should i do. Get rid of the Access database, just use it as the GUI. Jim "Jim Kennedy" <kennedy-downwithspammersfamily@attbi.net> wrote in message news:<A3GNc.174982$IQ4.4937@attbi_s02>... > "Varun" <varuns123@ggn.hcltech.com> wrote in message > news:a76d8c39.0407272029.5df9d058@posting.google.com... > > Hi all, > > well i have migrated the database from access to oracle and changes > > would be done in access only so i want to make same changes in the > > oracle database say after 10 mins. or so everytime i.e a continuous > > process either changes made or not so what should i do. > Get rid of the Access database, just use it as the GUI. > Jim but the problem is that the actual application that we are enhancing is using access and ...
how to access the queries made and stored in MS Access thru C# Code?
hi, Does anybody know how to access the queries made and stored in MS Access thru C# Code. thanks, bjorn Yes. You will need to use classes in namespaces System.Data System.Data.OleDb A quick example of a function that returns a dataset might be (unchecked) -------------------------------- using System.Data; using System.Data.OleDb; public class MyClass { string strConnect = "enter your OLEDB connection string here"; public static DataSet ExecuteDataset(string strSQL) { try { //create a command and prepare it for execution OleDbConnection objConn = new OleDbConnection(strConnect); OleDbDataAdapter objAdapt = new OleDbDataAdapter(strSQL, objConn); //Fill the dataset DataSet DS = new DataSet(); objAdapt.Fill(DS, "DataTable"); return DS; } catch (Exception ex) { //return nothing if an error has occurred return null; } } } --------------------------------- to use the function, you might do the following: DataSet objDS = myproject.MyClass.ExecuteDataset("SELECT * FROM qryMySavedQuery"); --------------------------------- There's a lot to learn in.NET so I'm not going to try to show you everything here. If you're not familiar with .NET, you will probably want to buy yourself a book and learn about it, especially the data access portion (System.Data, otherwise known as ADO.NET) of the framework. Bill E. Hollywood, FL ...
Accessing a passworded MS Access 2000 database in Crystal Reports #2
Can anybody give me some code that I can put in to my VB6 app to open a passworded Access database in the Crystal Reports 8.5 RDC? I see so many examples - none of which seem to work for me. I am using a native connection in Crystal ( pdbdao.dll). My current code is : Dim Report As New crPrintPreview Dim crTable As CRAXDRT.DatabaseTable Private Sub Form_Load() Set crTable = Report.Database.Tables.Item(1) varpath = CurDir crTable.Location = varpath & "\OrderManager.mdb" CRViewer1.ReportSource = Report CRViewer1.ViewReport End Sub All I receive is the message "Error opening file xxxxxxx etc" and then "Open Database session failed" Appreciate any help! Peter Tyler ...
MS Access Database
Interested profiles should be delivered at versatilehr.farook@gmail.com Title: MS Access Database Location-NC Duration-3 months Skills & Experience: MS Access Database experience: Must understand and have experience developing MS Access applications. =95 SQL experience =95 ODBC =95 Importing and exporting excel spreadsheets to and from Access DB =95 Forms development =95 working with DB objects =95 Creating New Databases The Relational Model of Data Storage Creating Relational Database Tables Assigning a Primary Key to a Table =95 Joining tables in Database =95 Queries and Advanced Queries using conditions =95 Macros =95 Reporting =95 Using Datasheets =95 Customizing tool bars Farook Ahmed On 19 May, 19:15, Farook Ahmed <farook.versatil...@gmail.com> wrote: > Interested profiles should be delivered at > versatilehr.far...@gmail.com > > Title: MS Access Database > Location-NC > Duration-3 months > > Skills & Experience: > MS Access Database experience: Must understand and have experience > developing MS Access applications. > =95 SQL experience > =95 ODBC > =95 Importing and exporting excel spreadsheets to and from Access DB > =95 Forms development > =95 working with DB objects > =95 Creating New Databases > =A0The Relational Model of Data Storage > Creating Relational Database Tables > Assigning a Primary Key to a Table > =95 Joining tables in Database > =95 Queries and Advanced Queries using c...
Problems with MOD-Function by accessing MS-Access Database via SQL in Delphi
Hello, the following problem: I use Delphi 6 to access a MS Access Database. In short, the Delphi Code looks like this: ADOConnection1.Open; ADOQuery1.Close; ADOQuery1.SQL.Text :=3D 'SELECT * FROM database1 WHERE value1=3D1 AND value2=3D ' + inttostr(array[3]); ADOQuery1.Open; Label5.Caption:=3DADOQuery1.Fiel=ADdByName('value4').AsString; Now I only want to select these rows, where the Integer value3 ends on 1=2E So I need the Modulo-Function. I found in the Internet these two functions: MOD(x,y) und x % y But I get an error if I want to use one. For example, If I change ADOQuery1.SQL.Text :=3D 'SELECT * FROM database1 WHERE value1=3D1 AND value2=3D ' + inttostr(array[3])+' AND MOD(value3,10) =3D 1; Delphi shows me a Syntax Error in this statement. Thx for help! Sorry, there is a ' missing in the code. But it has nothin to do with the problem: The first code runs perfectly, but If I insert AND MOD(20,10) = 0 there is the syntax error again. On 23 Aug 2005 11:51:15 -0700, sarah18web wrote: > Sorry, there is a ' missing in the code. But it has nothin to do with > the problem: > > The first code runs perfectly, but If I insert > AND MOD(20,10) = 0 there is the syntax error again. Don't know what it might have to do with Delphi; but MOD() is not a function supported by SQL server. Only the % operator works. ...
MS Access Script to import data from external mde database to mdb database
Hi, Does anyone know of a way I can write a script that imports a table from a particular .mde file? I don't seen any obvious way to do it as a macro. Any suggestions if it can be done as a macro or as a written module? My ultimate goal is to have script that clears info out of 1 table in an .mdb file, imports an identically structured table from a .mde file, runs an append query I have in place to copy the data from the imported table to the table in my.mbd file, and then delete the imported table. Any help that could be provided would be greatly appreciated. Best wishes, George Hadley ghadley_00@yahoo.com With DBEngine(0)(0) .Execute "DELETE * FROM Suppliers WHERE SupplierID IN (SELECT SupplierID FROM Northwind.mde.Suppliers)" .Execute "INSERT INTO Suppliers SELECT * FROM Northwind.mde.Suppliers" End With Is this code I would run as a module? Yes or you could use the strings in two separate saved queries. ...
How can I stop people have access to my MS Access Tables and Queries via Import?
Hi I have an MS Access based application almost ready for distribution to the public and I find that even though I have compiled it into an MDE file, tables and queries can still be be imported if accessed by another MS Access database. How can I stop this please? Regards Carriolan <carriolan@> wrote in message news:pvbbp19hrepj8d02pvdru31rgkvk0o8kg5@4ax.com... > Hi > I have an MS Access based application almost ready for distribution to > the public and I find that even though I have compiled it into an MDE > file, tables and queries can still be be imported if accessed by > another MS Access database. How can I stop this please? > User-level security. Not for the faint-hearted and not resistant to a determined hacker with the right tools but good enough to keep out nosey parkers. Check out the MS FAQ on Access security (link on my web site) and Google Access groups for Jeff Conrad who has some useful stuff too. HTH - Keith. www.keithwilby.com Hi Keith I am going through the MS FAQ as you suggested. Agreed it is not for the faint hearted! I have one more quested. As my program is for distratibution to the general public as shareware, is using this type of security valid? Regards Carriolan On Tue, 6 Dec 2005 16:06:02 -0000, "Keith W" <here@there.com> wrote: ><carriolan@> wrote in message >news:pvbbp19hrepj8d02pvdru31rgkvk0o8kg5@4ax.com... >> Hi >> I have an MS Access based application almost read...
Query in Access cannot be updated
Hi folks- this will be a piece of cake for you: The tables tblOne (Number, Name) tblTwo (Number, Revenue) are connected through a query, using one field as connector: qryThree (Number, Name, Revenue) The query's Name field can now not be updated. Because my knowledge about relational databases seems to be wiped out completely, please help me with the reason and a bypass for this problem! Using Access XP. Thanks mucho, Markus "Markus" <markus.erfert@epost.de> wrote in message news:d2bd7559.0309030654.15a272ca@posting.google.com... > Hi folks- > > this will be a piece of cake for you: > > The tables > tblOne (Number, Name) > tblTwo (Number, Revenue) > > are connected through a query, using one field as connector: > > qryThree (Number, Name, Revenue) > > The query's Name field can now not be updated. Because my knowledge > about relational databases seems to be wiped out completely, please > help me with the reason and a bypass for this problem! > > Using Access XP. > > Thanks mucho, > > Markus Just to start with, I would advise against naming fields 'Number' and 'Name' - there are times when this might cause unexpected results in Access. It might also help have fields uniquely identified so you can distinguish between tblOne.Number and tblTwo.Number. Anyway, I believe your problem lies in the indexing of the tables. Is 'Number' supposed to be the primary...
UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO
Hello, I am trying to update records in my database from excel data using vba editor within excel. In order to launch a query, I use SQL langage in ADO as follwing: ------------------------------------------------------------ Dim adoConn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim sConn As String Dim sSql As String Dim sOutput As String sConn = "DSN=MS Access Database;" & _ "DBQ=MyDatabasePath;" & _ "DefaultDir=MyPathDirectory;" & _ "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" & _ "PWD=xxxxxx;UID=admin;" ID, A, B C.. are my table fields sSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J, `K`, L" & _ " FROM MyTblName" & _ " WHERE (`A`='MyA')" & _ " AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})" & _ " ORDER BY `C` DESC" Set adoConn = New ADODB.Connection adoConn.Open sConn Set adoRs = New ADODB.Recordset adoRs.Open Source:=sSql, _ ActiveConnection:=adoConn adoRs.MoveFirst Sheets("Sheet1").Range("a2").CopyFromRecordset adoRs Set adoRs = Nothing Set adoConn = Nothing --------------------------------------------------------------- Does Anyone know How I can u...
UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO
Hello, I am trying to update records in my database from excel data using vba editor within excel. In order to launch a query, I use SQL langage in ADO as follwing: ------------------------------------------------------------ Dim adoConn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim sConn As String Dim sSql As String Dim sOutput As String sConn = "DSN=MS Access Database;" & _ "DBQ=MyDatabasePath;" & _ "DefaultDir=MyPathDirectory;" & _ "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" & _ "PWD=xxxxxx;UID=admin;" ID, A, B C.. are my table fields sSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J, `K`, L" & _ " FROM MyTblName" & _ " WHERE (`A`='MyA')" & _ " AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})" & _ " ORDER BY `C` DESC" Set adoConn = New ADODB.Connection adoConn.Open sConn Set adoRs = New ADODB.Recordset adoRs.Open Source:=sSql, _ ActiveConnection:=adoConn adoRs.MoveFirst Sheets("Sheet1").Range("a2").CopyFromRecordset adoRs Set adoRs = Nothing Set adoConn = Nothing --------------------------------------------------------------- Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statem...
UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO
Hello, I am trying to update records in my database from excel data using vba editor within excel. In order to launch a query, I use SQL langage in ADO as follwing: ------------------------------------------------------------ Dim adoConn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim sConn As String Dim sSql As String Dim sOutput As String sConn = "DSN=MS Access Database;" & _ "DBQ=MyDatabasePath;" & _ "DefaultDir=MyPathDirectory;" & _ "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" & _ "PWD=xxxxxx;UID=admin;" ID, A, B C.. are my table fields sSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J, `K`, L" & _ " FROM MyTblName" & _ " WHERE (`A`='MyA')" & _ " AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})" & _ " ORDER BY `C` DESC" Set adoConn = New ADODB.Connection adoConn.Open sConn Set adoRs = New ADODB.Recordset adoRs.Open Source:=sSql, _ ActiveConnection:=adoConn adoRs.MoveFirst Sheets("Sheet1").Range("a2").CopyFromRecordset adoRs Set adoRs = Nothing Set adoConn = Nothing --------------------------------------------------------------- Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statem...
Should I upgrade from MS Access 97 to MS Access 2002
Hi guys My company has in one of their products used Access 97 and MS JET 3.5 for several years now. Should we move the database to Access 2002 -which we use now in other parts of the company and also upgrade the MS JET to the latest version? And what does MS JET do? Kjell Arne Johansen MS Jet is the database engine that comes with, and is, by default, used by Microsoft Access. If you aren't having any troubles with the Access 97 database, then there isn't any technical reason to change. There may be company political reasons if, say, the IT department only wants to have one version of Access in-house. Another reason might be if you were concerned about getting support (error fix support) from Microsoft. Then again, when is the last time you had to call Microsoft support for this database? Larry Linson Microsoft Access MVP "Kjell Arne Johansen" <kjell.arne.johansen@kongsberg-simrad.com> wrote in message news:3f2f8600.699258150@news.eunet.no... > Hi guys > > My company has in one of their products used Access 97 and MS JET 3.5 > for several years now. > > Should we move the database to Access 2002 -which we use now in other > parts of the company and also upgrade the MS JET to the latest > version? > > And what does MS JET do? > > Kjell Arne Johansen "Kjell Arne Johansen" <kjell.arne.johansen@kongsberg-simrad.com> wrote in message news:3f2f8600.699258150@news.eunet.no... > Hi gu...
sql update/access query
A user has a make table query, basically it does some summing and creates a 'summary' table. I'd like to get rid of it (it's the last one in the access back-end) and use pure sql. Is the same thing easy enough to accomplish in sql or would you still need to make the table in the FE and then update the (same) table in sql..? -- Thanks, Lap "Lapchien" <blar@blar.tv> wrote in message news:1070747590.79206@ananke.eclipse.net.uk... > A user has a make table query, basically it does some summing and creates a > 'summary' table. I'd like to get rid of it (it's the last one in the access > back-end) and use pure sql. Is the same thing easy enough to accomplish in > sql or would you still need to make the table in the FE and then update the > (same) table in sql..? Depends on how it's used, what kinds of summaries it contains, if FE users have links stored to this summary table, etc. > -- > Thanks, > Lap > > When it's created (currently in the access BE) users have a link from their FE, nothing fancy, just to lookup. The summaries it contains are just currency transactions, basic 'sum' query expressions. -- Thanks, Chris cc8345@NOSPAMeclipse.co.uk "DFS" <nospamDS@nospam.com> wrote in message news:vt4n1ub4tbfjfc@corp.supernews.com... > "Lapchien" <blar@blar.tv> wrote in message > news:1070747590.79206@ananke.eclipse.net.uk... > >...
Datareport-How to query from MS Access
I need to run a data report that will query an Access_Table that has the following Project info: My Form has a command_button with the following code: Private Sub Command1_Click() Set db = New Connection db.CursorLocation = adUseClient db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Access_Table.mdb;" Set adoAge = New Recordset adoAge.Open "SELECT CustomerName, OrderNo, Invoice_Date, IIf([Invoice_Date]<Now()-30 And [Invoice_DATE]>Now()-60,[Invoice_Amount],"") AS Greaterthan30, IIf([Invoice_Date]<Now()-60,[Invoice_Amount],"") AS Greaterthan60 FROM Cust WHERE [Invoice_Date]< Now()-30", db, adOpenStatic, adLockOptimistic Set DataReport1.DataSource = adoAge DataReport1.Show End Sub My DataReport1 has the following RptTextbox: RptTextbox Datafield 1 CustomerName 2 OrderNo 3 Invoice_Date 4 Greaterthan30 5 Greaterthan60 Problem arise when trying to display the above report that has an error message that says "Datafield Greaterthan30 not found" Is this an SQL Query limitation where Invoice_Date Datafield can only be queried once & if we have more than one Datafield with the same fieldname (Invoice_Date) within the same Data report,the report will fail to run? Should there be one more New Recordset to hold a second Datafield (Invoice_Date)? can anyone help? Thanks F...
MS Access database in IFS
re my earlier question about starting an Access database in the Integrated File System: 1) I cannot use STRPCCMD because the request might be initiated by a dumb screen. 2) I have had no success with RUNRMTCMD 3) Can Qshell be used? I merely need the database running to perform the same function as an evoked RPG program, but the sytem IPLs on a Monday morning so ideally the initiation would be part of the IPL. Many thanks Peter Kinsman On further consideration, because the database is not an executable, I don't think the idea would work, because it would need the path to a copy of MSAccess.exe - which would have to be on a PC. Peter Kinsman wrote: > re my earlier question about starting an Access database in the Integrated > File System: > 1) I cannot use STRPCCMD because the request might be initiated by a dumb > screen. > 2) I have had no success with RUNRMTCMD > 3) Can Qshell be used? Peter: 1) Makes sense. 2) Can't comment since you give no indication what problems you've had with it. RUNRMTCMD is essentially the standard way of having one system execute a command on another system across a TCP/IP network. It doesn't matter what the platforms are (though the commands are different under different operating systems). 3) Qshell can be used if you want to use the rexec utility. It's essentially RUNRMTCMD in a Unix-like shell. If you don't like the easy RUNRMTCMD (or rexec) functions, I suppose you could alw...
Opening MS Access database
Hi, The problem is that while opening MS Access database via ADO/Jet4 with connection mode set to adModeShareDenyWrite, this should prevent others from opening in write mode. But it denies writing all (including me)! Adding adModeWrite allows to write but doesn't prevent others from writing... I want to open the database in share mode so that only one user can write while others can read. Is it possible? Regards, Rafal "Rafal Kaminski" <yksnimak@_usun_to_wp.pl> wrote in news:d6nnhj$cj6$1@news.onet.pl: > Hi, > > The problem is that while opening MS Access database via ADO/Jet4 with > connection mode set to adModeShareDenyWrite, this should > prevent others from opening in write mode. But it denies writing all > (including me)! Adding adModeWrite allows to write but doesn't prevent > others from writing... > I want to open the database in share mode so that only one user can > write while others can read. Is it possible? > If the table in the MS Access database has write permissions, then anyone can write a record to the table. Access is not a multi user database application in the first place and sooner or later the database will become corrupted and will have to be repaired. This happens on a routine basis that MS Access databases must be repaired because of multi user usage. You should look into using MS SQL Server, which there is a personal version of SQL Server that will run on a Windows NT based works...
Hi! Is there a MATLAB command which gives access to a .mdb MS Access database or do I need a special driver? Thanks, Andree Hi Andree Ellert, you can use activeXcontrol to interact with MS-Access database. access = actxserver('Access.Application'); returns the handle for Access. set(access, 'Visible', 1); will show the opened Access window. use get(access) and set(access) commands to find the methods available to call. -Vadivelu M =========== http://www.mathworks.com/access/helpdesk/help/toolbox/database/datatool.shtml "Andree Ellert" <ellert@gmx.net/////\\\\\> wrote in message news:eed9874.-1@webx.raydaftYaTP... > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree Andree Ellert wrote: > > > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree <http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=4045&objectType=file> -Hardik ...
Use ASP to read MS Access query that calls MS Access function
Hi: I have an MS Access query for a table called MyTable. One of my fields in the query is a custom field that looks like "MyField: = MyFunction(ID)" where ID is a field in MyTable. I have ASP code and I can use it to read the query OK when "MyField" is not part of the query. If I add in MyField and then run my ASP code to try to read all the query fields, I get an error message on my web browser which says something to the effect of "cannot find MyFunction." On the Access side, I have MyFunction set up in a module and use the keyword "Public" in front of the function name. What else do I need to do to get my ASP code to run this query. I'm trying to avoid reproducing MyFunction within the ASP code if possible. Thanks. Kevin On Thu, 14 Jun 2007 21:38:19 -0000, k-man <kmccrack@gmail.com> wrote: Sorry, no can do. From ASP you don't have access to the Access VBA interpreter. -Tom. >Hi: > >I have an MS Access query for a table called MyTable. One of my >fields in the query is a custom field that looks like "MyField: = >MyFunction(ID)" where ID is a field in MyTable. > >I have ASP code and I can use it to read the query OK when "MyField" >is not part of the query. If I add in MyField and then run my ASP >code to try to read all the query fields, I get an error message on my >web browser which says something to the effect of "cannot find >MyFunction."...
MS Access, Oracle 9i, security, and pass-thru update queries
Architecture: Access 2003 client, Oracle 9i repository, no Access security in place, ODBC linked tables. 100 or so users, in 3 or 4 groups (Oracle roles actually): Admins, Updaters and ReadOnly. Each group sees a different set of menu options when they open the client and login to Oracle. For the sake of speed I use pass-through queries here and there for updates and deletes. I update their SQL property in code and execute them. Since the pass-thru queries need Admin priveleges, I could/would like to store the Admin password with the query. But that's Security Breach #1, since you can import the query into another .mdb (even from the .mde), and see the Admin password. To get around the breach, I don't store the password with the query, and when Admins login I create a pass-thru query in code, and connect to the db with the Admin password, thus setting their permissions for the session. This works fine, but it's kind of kludgey. I'd like to protect the queries (and ODBC table links) from prying eyes, and I definitely won't be using native Access security. Ideas? Thanks DFS wrote: > Architecture: Access 2003 client, Oracle 9i repository, no Access security > in place, ODBC linked tables. > > 100 or so users, in 3 or 4 groups (Oracle roles actually): Admins, Updaters > and ReadOnly. Each group sees a different set of menu options when they > open the client and login to Oracle. > > For the sake of speed I use pass-thr...
newbie needs guidance from mastuh on querying mysql database from ms access... please
i've already set up mysql server and msaccess wif myodbc driver (myodbc 3.51) on my computer and i've created a new "database" (it only links to the tables in mysql server) in ms access. The problem comes when i try to do a UNION query from ms access on 3 or more linked mysql tables, it prints me an error... any ideas? It works when i do a union query only on 2 tables but not on more. i've checked that the number of fields in the tables and the field properties of each table are the the same, but i still get that error. thanx for your time "angel duran" <angelduran2004@hotmail.com> wrote in message news:a4e32a26.0408181126.5ee648cc@posting.google.com... > i've already set up mysql server and msaccess wif myodbc driver > (myodbc 3.51) on my computer and i've created a new "database" (it > only links to the tables in mysql server) in ms access. The problem > comes when i try to do a UNION query from ms access on 3 or more > linked mysql tables, it prints me an error... any ideas? It works when > i do a union query only on 2 tables but not on more. i've checked > that the number of fields in the tables and the field properties of > each table are the the same, but i still get that error. thanx for > your time MySQL Server = You get what you pay for. MyODBC Driver = You get what you pay for. Find out what "free" is all about at: http://www.mysql.com/support/techsupport.ht...
Excel database query does not work after securing an Access database
Hi all, I'd appreciate if someone could give me a hint to this little annoying problem: I have a simple database that I want to be able to query from Excel. When I use the "new database query" wizard on an unsecured version of my database, everything works fine. I am able to read the tables and queries, I can build customized queries in Excel from the Access tables/queries. Now, the moment I secure my database I loose all connectiviity between Excel and Access. The wizard won't even connect to the database. When I add permission to "open/read database" object through "Users and Groups Permission" in Access, I am able to connect to the database and see all the tables/queries, but it does not show the fields. I thought that in theory if I add permissions to "read design" and "read data" to my tables and queries for the regular users that should solve the problem, but it doesn't. I am obviously missing something somewhere, but what? I searched the web but could not find anyting. I am using Windows XP Pro SP2 and Access 2003 and Excel 2003. Any advice???? Michael I'm actually just facing this problem as well myself, and have no answer. I have a secure database that's split front and backend. I've had split secure databases before where I have been able to query via excel, but for some reason, this one won't let me. ...
access MS-Access database in C# Windows Application
Hi, I am trying to access the MS Access database on my local machine in C# Windows Application in Visual Studio.NET. But it seems that it does not work. I use the Microsoft Application Blocks to access the database. I have tried 2 possible connection strings. ----------------------------------------- str_1 = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" + DBFilePath; str_2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBFilePath; //SqlHelper is the class in the Microsoft Application Blocks SqlHelper.ExecuteNonQuery( str_1, CommandType.Text, SQL_str); ----------------------------------- The error message I got is: "Unknown connection option in connection string: driver." for str_1 and " Unknown connection option in connection string: provider." for str_2 So I am wondering, where is the problem? Is it because my connection string for MS Access is specified wrongly? Or is it because I cannot use SqlHelper to access the MS Access database? ( I have used SqlHelper class to access the MS SQL server, and it works) Your help will be greatly appreciated. James James wrote: > Hi, I am trying to access the MS Access database on my local machine > in C# Windows Application in Visual Studio.NET. > > But it seems that it does not work. I use the Microsoft Application > Blocks to access the database. > > I have tried 2 possible connection strings. > > ----------------------------------------- > ...
How to reset or disconnect all conections to an MS Access database if the database is hung up?
Hi, All: Sometime I do not knwo why my database in local C drive is locked. If I restart the LabVIEW software, it works fine. Is there a way to disconnect all connections before you make a new connection? I remember I used this function a couple of years ago but I could not find the function in new LabVIEW database connectivity tool kit. I am using LabVIEW 8.0.1. Thanks. Jim Is it DB Close Connection? You VI should contain one of these. <img src="http://forums.ni.com/attachments/ni/170/194552/1/DB%20Close%20Connection.jpg"> DavidMessage Edited by David Crawford on 07-11-2006 07:38 AM DB Close Connection.jpg: http://forums.ni.com/attachments/ni/170/194552/1/DB Close Connection.jpg David: Thanks for your posting. I?did use the functions you mentioned. I?want to know if you can disconnect all database connections if you somehow forget to disconnect. Anyway, my problem has been solved. The reason is the data source name missing after some operation. Thanks again. Jim ...
Accessing a passworded MS Access 2000 database in Crystal Reports
Can anybody give me some code that I can put in to my VB6 app to open a passworded Access database in the Crystal Reports 8.5 RDC? I see so many examples - none of which seem to work for me. I am using a native connection in Crystal ( pdbdao.dll). My current code is : Dim Report As New crPrintPreview Dim crTable As CRAXDRT.DatabaseTable Private Sub Form_Load() Set crTable = Report.Database.Tables.Item(1) varpath = CurDir crTable.Location = varpath & "\OrderManager.mdb" CRViewer1.ReportSource = Report CRViewer1.ViewReport End Sub All I receive is the message "Error opening file xxxxxxx etc" and then "Open Database session failed" Appreciate any help! Peter Tyler ...
update oracle database when ever changes are made in access database
Hi all, well i have migrated the database from access to oracle and changes would be done in access only so i want to make same changes in the oracle database say after 10 mins. or so everytime i.e a continuous process either changes made or not so what should i do. "Varun" <varuns123@ggn.hcltech.com> wrote in message news:a76d8c39.0407272029.5df9d058@posting.google.com... > Hi all, > well i have migrated the database from access to oracle and changes > would be done in access only so i want to make same changes in the > oracle database say after 10 mins. or so everytime i.e a continuous > process either changes made or not so what should i do. Get rid of the Access database, just use it as the GUI. Jim "Jim Kennedy" <kennedy-downwithspammersfamily@attbi.net> wrote in message news:<A3GNc.174982$IQ4.4937@attbi_s02>... > "Varun" <varuns123@ggn.hcltech.com> wrote in message > news:a76d8c39.0407272029.5df9d058@posting.google.com... > > Hi all, > > well i have migrated the database from access to oracle and changes > > would be done in access only so i want to make same changes in the > > oracle database say after 10 mins. or so everytime i.e a continuous > > process either changes made or not so what should i do. > Get rid of the Access database, just use it as the GUI. > Jim but the problem is that the actual application that we are enhancing is using access and ...
how to access the queries made and stored in MS Access thru C# Code?
hi, Does anybody know how to access the queries made and stored in MS Access thru C# Code. thanks, bjorn Yes. You will need to use classes in namespaces System.Data System.Data.OleDb A quick example of a function that returns a dataset might be (unchecked) -------------------------------- using System.Data; using System.Data.OleDb; public class MyClass { string strConnect = "enter your OLEDB connection string here"; public static DataSet ExecuteDataset(string strSQL) { try { //create a command and prepare it for execution OleDbConnection objConn = new OleDbConnection(strConnect); OleDbDataAdapter objAdapt = new OleDbDataAdapter(strSQL, objConn); //Fill the dataset DataSet DS = new DataSet(); objAdapt.Fill(DS, "DataTable"); return DS; } catch (Exception ex) { //return nothing if an error has occurred return null; } } } --------------------------------- to use the function, you might do the following: DataSet objDS = myproject.MyClass.ExecuteDataset("SELECT * FROM qryMySavedQuery"); --------------------------------- There's a lot to learn in.NET so I'm not going to try to show you everything here. If you're not familiar with .NET, you will probably want to buy yourself a book and learn about it, especially the data access portion (System.Data, otherwise known as ADO.NET) of the framework. Bill E. Hollywood, FL ...
Accessing a passworded MS Access 2000 database in Crystal Reports #2
Can anybody give me some code that I can put in to my VB6 app to open a passworded Access database in the Crystal Reports 8.5 RDC? I see so many examples - none of which seem to work for me. I am using a native connection in Crystal ( pdbdao.dll). My current code is : Dim Report As New crPrintPreview Dim crTable As CRAXDRT.DatabaseTable Private Sub Form_Load() Set crTable = Report.Database.Tables.Item(1) varpath = CurDir crTable.Location = varpath & "\OrderManager.mdb" CRViewer1.ReportSource = Report CRViewer1.ViewReport End Sub All I receive is the message "Error opening file xxxxxxx etc" and then "Open Database session failed" Appreciate any help! Peter Tyler ...
MS Access Database
Interested profiles should be delivered at versatilehr.farook@gmail.com Title: MS Access Database Location-NC Duration-3 months Skills & Experience: MS Access Database experience: Must understand and have experience developing MS Access applications. =95 SQL experience =95 ODBC =95 Importing and exporting excel spreadsheets to and from Access DB =95 Forms development =95 working with DB objects =95 Creating New Databases The Relational Model of Data Storage Creating Relational Database Tables Assigning a Primary Key to a Table =95 Joining tables in Database =95 Queries and Advanced Queries using conditions =95 Macros =95 Reporting =95 Using Datasheets =95 Customizing tool bars Farook Ahmed On 19 May, 19:15, Farook Ahmed <farook.versatil...@gmail.com> wrote: > Interested profiles should be delivered at > versatilehr.far...@gmail.com > > Title: MS Access Database > Location-NC > Duration-3 months > > Skills & Experience: > MS Access Database experience: Must understand and have experience > developing MS Access applications. > =95 SQL experience > =95 ODBC > =95 Importing and exporting excel spreadsheets to and from Access DB > =95 Forms development > =95 working with DB objects > =95 Creating New Databases > =A0The Relational Model of Data Storage > Creating Relational Database Tables > Assigning a Primary Key to a Table > =95 Joining tables in Database > =95 Queries and Advanced Queries using c...
Problems with MOD-Function by accessing MS-Access Database via SQL in Delphi
Hello, the following problem: I use Delphi 6 to access a MS Access Database. In short, the Delphi Code looks like this: ADOConnection1.Open; ADOQuery1.Close; ADOQuery1.SQL.Text :=3D 'SELECT * FROM database1 WHERE value1=3D1 AND value2=3D ' + inttostr(array[3]); ADOQuery1.Open; Label5.Caption:=3DADOQuery1.Fiel=ADdByName('value4').AsString; Now I only want to select these rows, where the Integer value3 ends on 1=2E So I need the Modulo-Function. I found in the Internet these two functions: MOD(x,y) und x % y But I get an error if I want to use one. For example, If I change ADOQuery1.SQL.Text :=3D 'SELECT * FROM database1 WHERE value1=3D1 AND value2=3D ' + inttostr(array[3])+' AND MOD(value3,10) =3D 1; Delphi shows me a Syntax Error in this statement. Thx for help! Sorry, there is a ' missing in the code. But it has nothin to do with the problem: The first code runs perfectly, but If I insert AND MOD(20,10) = 0 there is the syntax error again. On 23 Aug 2005 11:51:15 -0700, sarah18web wrote: > Sorry, there is a ' missing in the code. But it has nothin to do with > the problem: > > The first code runs perfectly, but If I insert > AND MOD(20,10) = 0 there is the syntax error again. Don't know what it might have to do with Delphi; but MOD() is not a function supported by SQL server. Only the % operator works. ...
MS Access Script to import data from external mde database to mdb database
Hi, Does anyone know of a way I can write a script that imports a table from a particular .mde file? I don't seen any obvious way to do it as a macro. Any suggestions if it can be done as a macro or as a written module? My ultimate goal is to have script that clears info out of 1 table in an .mdb file, imports an identically structured table from a .mde file, runs an append query I have in place to copy the data from the imported table to the table in my.mbd file, and then delete the imported table. Any help that could be provided would be greatly appreciated. Best wishes, George Hadley ghadley_00@yahoo.com With DBEngine(0)(0) .Execute "DELETE * FROM Suppliers WHERE SupplierID IN (SELECT SupplierID FROM Northwind.mde.Suppliers)" .Execute "INSERT INTO Suppliers SELECT * FROM Northwind.mde.Suppliers" End With Is this code I would run as a module? Yes or you could use the strings in two separate saved queries. ...
How can I stop people have access to my MS Access Tables and Queries via Import?
Hi I have an MS Access based application almost ready for distribution to the public and I find that even though I have compiled it into an MDE file, tables and queries can still be be imported if accessed by another MS Access database. How can I stop this please? Regards Carriolan <carriolan@> wrote in message news:pvbbp19hrepj8d02pvdru31rgkvk0o8kg5@4ax.com... > Hi > I have an MS Access based application almost ready for distribution to > the public and I find that even though I have compiled it into an MDE > file, tables and queries can still be be imported if accessed by > another MS Access database. How can I stop this please? > User-level security. Not for the faint-hearted and not resistant to a determined hacker with the right tools but good enough to keep out nosey parkers. Check out the MS FAQ on Access security (link on my web site) and Google Access groups for Jeff Conrad who has some useful stuff too. HTH - Keith. www.keithwilby.com Hi Keith I am going through the MS FAQ as you suggested. Agreed it is not for the faint hearted! I have one more quested. As my program is for distratibution to the general public as shareware, is using this type of security valid? Regards Carriolan On Tue, 6 Dec 2005 16:06:02 -0000, "Keith W" <here@there.com> wrote: ><carriolan@> wrote in message >news:pvbbp19hrepj8d02pvdru31rgkvk0o8kg5@4ax.com... >> Hi >> I have an MS Access based application almost read...
Query in Access cannot be updated
Hi folks- this will be a piece of cake for you: The tables tblOne (Number, Name) tblTwo (Number, Revenue) are connected through a query, using one field as connector: qryThree (Number, Name, Revenue) The query's Name field can now not be updated. Because my knowledge about relational databases seems to be wiped out completely, please help me with the reason and a bypass for this problem! Using Access XP. Thanks mucho, Markus "Markus" <markus.erfert@epost.de> wrote in message news:d2bd7559.0309030654.15a272ca@posting.google.com... > Hi folks- > > this will be a piece of cake for you: > > The tables > tblOne (Number, Name) > tblTwo (Number, Revenue) > > are connected through a query, using one field as connector: > > qryThree (Number, Name, Revenue) > > The query's Name field can now not be updated. Because my knowledge > about relational databases seems to be wiped out completely, please > help me with the reason and a bypass for this problem! > > Using Access XP. > > Thanks mucho, > > Markus Just to start with, I would advise against naming fields 'Number' and 'Name' - there are times when this might cause unexpected results in Access. It might also help have fields uniquely identified so you can distinguish between tblOne.Number and tblTwo.Number. Anyway, I believe your problem lies in the indexing of the tables. Is 'Number' supposed to be the primary...
UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO
Hello, I am trying to update records in my database from excel data using vba editor within excel. In order to launch a query, I use SQL langage in ADO as follwing: ------------------------------------------------------------ Dim adoConn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim sConn As String Dim sSql As String Dim sOutput As String sConn = "DSN=MS Access Database;" & _ "DBQ=MyDatabasePath;" & _ "DefaultDir=MyPathDirectory;" & _ "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" & _ "PWD=xxxxxx;UID=admin;" ID, A, B C.. are my table fields sSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J, `K`, L" & _ " FROM MyTblName" & _ " WHERE (`A`='MyA')" & _ " AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})" & _ " ORDER BY `C` DESC" Set adoConn = New ADODB.Connection adoConn.Open sConn Set adoRs = New ADODB.Recordset adoRs.Open Source:=sSql, _ ActiveConnection:=adoConn adoRs.MoveFirst Sheets("Sheet1").Range("a2").CopyFromRecordset adoRs Set adoRs = Nothing Set adoConn = Nothing --------------------------------------------------------------- Does Anyone know How I can u...
UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO
Hello, I am trying to update records in my database from excel data using vba editor within excel. In order to launch a query, I use SQL langage in ADO as follwing: ------------------------------------------------------------ Dim adoConn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim sConn As String Dim sSql As String Dim sOutput As String sConn = "DSN=MS Access Database;" & _ "DBQ=MyDatabasePath;" & _ "DefaultDir=MyPathDirectory;" & _ "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" & _ "PWD=xxxxxx;UID=admin;" ID, A, B C.. are my table fields sSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J, `K`, L" & _ " FROM MyTblName" & _ " WHERE (`A`='MyA')" & _ " AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})" & _ " ORDER BY `C` DESC" Set adoConn = New ADODB.Connection adoConn.Open sConn Set adoRs = New ADODB.Recordset adoRs.Open Source:=sSql, _ ActiveConnection:=adoConn adoRs.MoveFirst Sheets("Sheet1").Range("a2").CopyFromRecordset adoRs Set adoRs = Nothing Set adoConn = Nothing --------------------------------------------------------------- Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statem...
UPDATE SQL Statement in Excel VBA Editor to update Access Database - ADO
Hello, I am trying to update records in my database from excel data using vba editor within excel. In order to launch a query, I use SQL langage in ADO as follwing: ------------------------------------------------------------ Dim adoConn As ADODB.Connection Dim adoRs As ADODB.Recordset Dim sConn As String Dim sSql As String Dim sOutput As String sConn = "DSN=MS Access Database;" & _ "DBQ=MyDatabasePath;" & _ "DefaultDir=MyPathDirectory;" & _ "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" & _ "PWD=xxxxxx;UID=admin;" ID, A, B C.. are my table fields sSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J, `K`, L" & _ " FROM MyTblName" & _ " WHERE (`A`='MyA')" & _ " AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})" & _ " ORDER BY `C` DESC" Set adoConn = New ADODB.Connection adoConn.Open sConn Set adoRs = New ADODB.Recordset adoRs.Open Source:=sSql, _ ActiveConnection:=adoConn adoRs.MoveFirst Sheets("Sheet1").Range("a2").CopyFromRecordset adoRs Set adoRs = Nothing Set adoConn = Nothing --------------------------------------------------------------- Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statem...
Should I upgrade from MS Access 97 to MS Access 2002
Hi guys My company has in one of their products used Access 97 and MS JET 3.5 for several years now. Should we move the database to Access 2002 -which we use now in other parts of the company and also upgrade the MS JET to the latest version? And what does MS JET do? Kjell Arne Johansen MS Jet is the database engine that comes with, and is, by default, used by Microsoft Access. If you aren't having any troubles with the Access 97 database, then there isn't any technical reason to change. There may be company political reasons if, say, the IT department only wants to have one version of Access in-house. Another reason might be if you were concerned about getting support (error fix support) from Microsoft. Then again, when is the last time you had to call Microsoft support for this database? Larry Linson Microsoft Access MVP "Kjell Arne Johansen" <kjell.arne.johansen@kongsberg-simrad.com> wrote in message news:3f2f8600.699258150@news.eunet.no... > Hi guys > > My company has in one of their products used Access 97 and MS JET 3.5 > for several years now. > > Should we move the database to Access 2002 -which we use now in other > parts of the company and also upgrade the MS JET to the latest > version? > > And what does MS JET do? > > Kjell Arne Johansen "Kjell Arne Johansen" <kjell.arne.johansen@kongsberg-simrad.com> wrote in message news:3f2f8600.699258150@news.eunet.no... > Hi gu...
sql update/access query
A user has a make table query, basically it does some summing and creates a 'summary' table. I'd like to get rid of it (it's the last one in the access back-end) and use pure sql. Is the same thing easy enough to accomplish in sql or would you still need to make the table in the FE and then update the (same) table in sql..? -- Thanks, Lap "Lapchien" <blar@blar.tv> wrote in message news:1070747590.79206@ananke.eclipse.net.uk... > A user has a make table query, basically it does some summing and creates a > 'summary' table. I'd like to get rid of it (it's the last one in the access > back-end) and use pure sql. Is the same thing easy enough to accomplish in > sql or would you still need to make the table in the FE and then update the > (same) table in sql..? Depends on how it's used, what kinds of summaries it contains, if FE users have links stored to this summary table, etc. > -- > Thanks, > Lap > > When it's created (currently in the access BE) users have a link from their FE, nothing fancy, just to lookup. The summaries it contains are just currency transactions, basic 'sum' query expressions. -- Thanks, Chris cc8345@NOSPAMeclipse.co.uk "DFS" <nospamDS@nospam.com> wrote in message news:vt4n1ub4tbfjfc@corp.supernews.com... > "Lapchien" <blar@blar.tv> wrote in message > news:1070747590.79206@ananke.eclipse.net.uk... > >...
Datareport-How to query from MS Access
I need to run a data report that will query an Access_Table that has the following Project info: My Form has a command_button with the following code: Private Sub Command1_Click() Set db = New Connection db.CursorLocation = adUseClient db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Access_Table.mdb;" Set adoAge = New Recordset adoAge.Open "SELECT CustomerName, OrderNo, Invoice_Date, IIf([Invoice_Date]<Now()-30 And [Invoice_DATE]>Now()-60,[Invoice_Amount],"") AS Greaterthan30, IIf([Invoice_Date]<Now()-60,[Invoice_Amount],"") AS Greaterthan60 FROM Cust WHERE [Invoice_Date]< Now()-30", db, adOpenStatic, adLockOptimistic Set DataReport1.DataSource = adoAge DataReport1.Show End Sub My DataReport1 has the following RptTextbox: RptTextbox Datafield 1 CustomerName 2 OrderNo 3 Invoice_Date 4 Greaterthan30 5 Greaterthan60 Problem arise when trying to display the above report that has an error message that says "Datafield Greaterthan30 not found" Is this an SQL Query limitation where Invoice_Date Datafield can only be queried once & if we have more than one Datafield with the same fieldname (Invoice_Date) within the same Data report,the report will fail to run? Should there be one more New Recordset to hold a second Datafield (Invoice_Date)? can anyone help? Thanks F...
MS Access database in IFS
re my earlier question about starting an Access database in the Integrated File System: 1) I cannot use STRPCCMD because the request might be initiated by a dumb screen. 2) I have had no success with RUNRMTCMD 3) Can Qshell be used? I merely need the database running to perform the same function as an evoked RPG program, but the sytem IPLs on a Monday morning so ideally the initiation would be part of the IPL. Many thanks Peter Kinsman On further consideration, because the database is not an executable, I don't think the idea would work, because it would need the path to a copy of MSAccess.exe - which would have to be on a PC. Peter Kinsman wrote: > re my earlier question about starting an Access database in the Integrated > File System: > 1) I cannot use STRPCCMD because the request might be initiated by a dumb > screen. > 2) I have had no success with RUNRMTCMD > 3) Can Qshell be used? Peter: 1) Makes sense. 2) Can't comment since you give no indication what problems you've had with it. RUNRMTCMD is essentially the standard way of having one system execute a command on another system across a TCP/IP network. It doesn't matter what the platforms are (though the commands are different under different operating systems). 3) Qshell can be used if you want to use the rexec utility. It's essentially RUNRMTCMD in a Unix-like shell. If you don't like the easy RUNRMTCMD (or rexec) functions, I suppose you could alw...
Opening MS Access database
Hi, The problem is that while opening MS Access database via ADO/Jet4 with connection mode set to adModeShareDenyWrite, this should prevent others from opening in write mode. But it denies writing all (including me)! Adding adModeWrite allows to write but doesn't prevent others from writing... I want to open the database in share mode so that only one user can write while others can read. Is it possible? Regards, Rafal "Rafal Kaminski" <yksnimak@_usun_to_wp.pl> wrote in news:d6nnhj$cj6$1@news.onet.pl: > Hi, > > The problem is that while opening MS Access database via ADO/Jet4 with > connection mode set to adModeShareDenyWrite, this should > prevent others from opening in write mode. But it denies writing all > (including me)! Adding adModeWrite allows to write but doesn't prevent > others from writing... > I want to open the database in share mode so that only one user can > write while others can read. Is it possible? > If the table in the MS Access database has write permissions, then anyone can write a record to the table. Access is not a multi user database application in the first place and sooner or later the database will become corrupted and will have to be repaired. This happens on a routine basis that MS Access databases must be repaired because of multi user usage. You should look into using MS SQL Server, which there is a personal version of SQL Server that will run on a Windows NT based works...
Web resources about - Updating MS-Access Database query - comp.soft-sys.matlabResources last updated: 3/30/2016 11:49:10 PM