We know the MS Access database engine is 'throttled' to allow a maximum file size of 2GB (or perhaps internally wired to be limited to fewer than some power of 2 of 4KB data pages). But what does this mean in practical terms? To help me measure this, can you tell me the maximum number of rows that can be inserted into a MS Access databa...
I need to replace this structure: dim base as dao.database set base = opendatabase ("c:\path\name.mdb") with another in which the .mdb is located in a server directory. I tried: dim base as dao.database set base = opendatabase ("\\server\folder\name.mdb") but it did not work. I'm using VB6. Any ideas? ...
I have written this query: UPDATE tbl_stock1 SET tbl_stock1.weight1 = ( select (b.weight1 - c.weight_in_gram) as temp from tbl_stock1 as b, tbl_sales_item as c where b.item_submodel_id = c.item_submodel_id and b.item_submodel_id = tbl_stock1.item_submodel_id and b.status <> 'D' ...
I have a list of orders with suboperations. How can I create a list of finished orders? Finished order must have finished all suboperations. Table "orders": order_no | suboperation | finished 1 | preparing | 01/01/2009 1 | scrubbing | 01/05/2009 1 | painting | 01/10/2009 2 | preparing | 02/05/09...
I've done extensive research on this topic and intensive discussions with my co-workers. The general consensus seems to be, that a database connection should be opend when needed and closed immediately. Any caching/pooling of connections should be done by the database driver or some other layer, but not by the application itself. But ...
I'm trying to execute a statement like: UPDATE table1 SET name="Joe" WHERE id='1'; UPDATE table2 SET name="Bob" WHERE id='2' But, when executing the query, I get: Microsoft JET Database Engine error '80040e14' Characters found after end of SQL statement. It works when I execute the statements separately, though. So I'm guessing the...
Background: I have one Access database (.mdb) file, with half a dozen tables in it. This file is ~300MB large, so not huge, but big enough that I want to be efficient. In it, there is one major table, a client table. The other tables store data like consultations made, a few extra many-to-one to one fields, that sort of thing. Task: I...
Hi all, I'm planning a new ASP.NET project that will become a product that is installed by techinical staff that work in various companies. The program will be storing sensitive information that shouldn't be easily accessible to staff including the technical staff. I need to balance an easy straight forward installation with security a...
I am trying to deploy GAL Modifier, which is an ASP.NET website which uses Microsoft Access on a Windows 2003 x64 machine. However there is no JET driver on x64 (see here), so the change is to use change the target CPU to x86. However as it is a web site there is no option in Visual Studio except Any CPU, so how can I change the settin...
Excel's NETWORKDAYS function returns the net working days between two dates. Is there a way of replicating this when connecting to a Jet database via ADO? ...
I'm having a problem with Jet throwing error 3011 when I try to use it to open a file with 2 "extensions" ("filename.tst.csv"). Run-time error '3011' The Microsoft Jet database engine could not find the object 'filename.tst.csv'. Make sure the object exists and that you spell its name and the path name correctly. Where the cod...
Hello I need to write a program that checks a access .mdb file and when a specific row is added (say the "name" field column of the newly added a row that contains a specific string, it should send an email to a specific address. For this I am planning to use "Jackcess" library for reading the mdb file and "Apache's commons-email libra...
Hi, I'm needing to import an Excel spreadsheet into my program and have the following code: string connectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;""", MyExcelFile.xls); command.CommandText = "SELECT * FROM [Sheet1$]"; (Note, code above isn't real cod...
I am using an ExcelProvider with Microsoft.Jet.OLEDB.4.0 and am getting the Cannot Find Installable ISAM from the production machine. Windows Server 2003. I've searched and search for this download. I've Office installed on the Dev machine, so I'm sure that's why it works fine on my end. But I can't install Office on the Production m...
I'm trying to change a password with a DDL statement like: CurrentProject.Connection.Execute "ALTER USER barney PASSWORD "[]!@#$%^ oldpassword" Yes, that's a nasty password, but someone tried something like that. Notice the beginning quote of the password is not part of the sql syntax here. I need something like mysql_real_escape_st...
I found this code online to query Access and input the data into excel (2003), but it is much slower than it should be: Sub DataPull(SQLQuery, CellPaste) Dim Con As New ADODB.Connection Dim RST As New ADODB.Recordset Dim DBlocation As String, DBName As String Dim ContractingQuery As String If SQLQuery = "" Then Else DBName = Range...
I have data in Excel files that I am pulling out using the MS Jet OLEDB provider. The results of my select are being passed into a component that I cannot change to generate an html table on the fly. Some of my data files contain numbers that I need formatted with commas (#,###). As I said I cannot change the output component to forma...
Hello, I am working on Windows 2008 core R2 and 32 bit. While running application in which I am trying to connect to .mdb file by ADODB connection and using provider as 'Microsoft Jet Provider 4.0' . To get the connection done Msjetoldb40.dll is necessary. But it is not present in Windows 2008 core R2 and 32-bit ( which is of only com...
I have a JET table with an auto-number as the primary key, and I would like to know how I can retrieve this number after inserting a row. I have thought of using MAX() to retrieve the row with the highest value, but am not sure how reliable this would be. Some sample code: Dim query As String Dim newRow As Integer query = "INSERT INTO...
What is the fastest way to import data into a SQL Server database directly from the active worksheet in Excel? I have had great success simply looping through the rows and columns, generating a SQL string, opening an ADODB.Connection and executing the SQL. That solution, however, is too slow for large datasets. So I am testing the Jet ...