|
Hello everybody,
I have wrote a database application that connects via ODBC to MS Access databases. After running many SQL queries on the databases, their size grows considerably (a 320kb file can grow to MB sizes). If I run the 'Repair and compress'-operation in Access, the databases shrink back to their expected size.
I tried switching from ODBC to OLEDB, but the problem remained unsolved...
Has anyone had a similar problem with Access databases?
I'd really appreciate if you could help me solve this problem.
Thanks,
paul
|
|
|
|
|
rocas wrote:
Has anyone had a similar problem with Access databases?
Sounds like normal database behaviour to me.
As you perform actions on a database it creates transactions so that the database will be in a consistent state should anything go wrong. These transactions will be appended to the end of the file until you clear out the transaction log, in this case by running "Repair and Compress".
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I had no problems in accessing information via Access Database using VB.Net. I created several datagrid programs accessing the MS Access database without an exception. I am just having problems in authenticating a login page that I am trying to use to open a secured program, that continues to point to the ExecuteReader being closed.
bravo659
|
|
|
|
|
I thought of the same explanation.
But how can I solve this problem. Is there an option for the connectstring that I can use to disable the log, or a method to programmatically call "Repair and Compress"?
|
|
|
|
|
Can we Pass the Array to the Procedure as a Parameter so as to insert multiple records at a time
Ex. We collected some 15 product when we insert thease in one click in one transaction table by Product(15)
Mail me
dhakane@gmail.com
Jeetendra Dhakane
|
|
|
|
|
What in the world does "AFAIK" mean!!
|
|
|
|
|
|
it is an acronym for "As Far As I Know"
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Thanks! That makes sense. Every time I came across this acronym, I would think "hmm, there goes Microsoft again...".
TYFYH (Thank You For Your Help).
|
|
|
|
|
|
Colin Angus Mackay wrote:
You didn't get my little joke
lol...no I did not...
now I do...
As far as I know (AFAIK) this question has nothing to do with databases, SQL or ADO.NET
I honestly believed it was one of the feature within .NET (hence - the reference to microsoft) that I did not know about! Boy, do I feel dumb.
|
|
|
|
|
|
How do I rename a table in SQL Server 2000? I mean by script, I can do it easily enough in the Enterprise Manager.
Cheers,
Andy
|
|
|
|
|
|
hello, everybody ...
recently, I've been writing a data-access application in C# which works with MS Access database...
in my DB, i've got some fields of OLE Object data type...but i haven't got a clue how to retireve, insert and modify them programmatically...especially MS Word documents, Text files, MS Excel Worksheets.......
Does any body have any idea how to extract ole objec( .doc, .xls, .txt, .rtf and so on ) from MS Access database and vice versa... how to insert those types of files into Access......
I will really appreciate if you can give me a hand...
Alex Tian
|
|
|
|
|
Helll All
Can anyone guide me about how will ADO connection string for connecting to a named instance Sql server over internet, I am wondering do I need to mention instance name too or not, and if I don't mention named instance what will happen if there are more than one instance of Sql Server running on the same machine (in that case all will have same ip address).
Please guide me and reply ASAP.
I am stuck up...
|
|
|
|
|
I wrote this code to create an excel file using data retrieved from either SQL Server
or Microsoft Access. Here is the practice code that I wrote. When you execute the application it will create an excel file with the data from SQL server or Access database which is inserted into the excel worksheet. However, when you examine the cells in Excel you will notice that all the cells that contain text have an apostrophe (' single quote) character at the begining of cell. The cells that have integers or float numbers do not have any ' character at the beining of the cell. I like to know if anyone knows how to fix this issue. What changes do I have to make in order Please take a look at my code and let me know as to how I can fix it. Why does apostrophe get inserted into cells that have text.
/*
*
*/
// csc /target:exe /optimize+ /nologo WriteToExcel.cs
using System;
using System.Data;
using System.Data.OleDb;
namespace writetoexcel
{
public class WriteToExcel
{
private static String connectMDB;
private static OleDbConnection conMDB;
private static String connectxls;
private static OleDbConnection conxls;
[STAThread]
public static void Main (String[] args)
{
try
{
connectMDB = "Provider=Microsoft.JET.OLEDB.4.0;data source=jewelrycastle_db.mdb";
conMDB = new OleDbConnection(connectMDB);
conMDB.Open();
Console.WriteLine("Made the connection to the my access mdb database");
connectxls = "Provider=Microsoft.Jet.OLEDB.4.0;data source=froogle.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"";
conxls = new OleDbConnection(connectxls);
conxls.Open();
Console.WriteLine("Made the connection to the froogle.xls file");
String query = "SELECT b.JewelryType, b.Category, b.Metal, b.itemNumber, b.Title, b.Description, b.ourPrice, b.vendorNumber "
+ "FROM Bracelets AS b WHERE b.VIEW = TRUE UNION " +
"SELECT e.JewelryType, e.Category, e.Metal, e.itemNumber, e.Title, e.Description, e.ourPrice, e.vendorNumber "
+ "FROM Earrings AS e WHERE e.VIEW = TRUE UNION " +
"SELECT n.JewelryType, n.Category, n.Metal, n.itemNumber, n.Title, n.Description, n.ourPrice, n.vendorNumber "
+ "FROM Necklaces AS n WHERE n.VIEW = TRUE UNION " +
"SELECT p.JewelryType, p.Category, p.Metal, p.itemNumber, p.Title, p.Description, p.ourPrice, p.vendorNumber "
+ "FROM Pendants AS p WHERE p.VIEW = TRUE UNION " +
"SELECT r.JewelryType, r.Category, r.Metal, r.itemNumber, r.Title, r.Description, r.ourPrice, r.vendorNumber "
+ "FROM Rings AS r WHERE r.VIEW = TRUE";
OleDbDataAdapter adapter = new OleDbDataAdapter(); // Note 8
adapter.SelectCommand = new OleDbCommand(query, conMDB); // Note 9
DataSet ds = new DataSet("myaccess");
adapter.Fill(ds); // Note 10
DataTable inventoryTable = ds.Tables[0];
OleDbCommand cmd = conxls.CreateCommand();
// cmd.CommandText = "DROP TABLE [Sheet1$]";
// cmd.ExecuteNonQuery();
// Console.WriteLine("Drop Sheet1");
// cmd.CommandText = "DROP TABLE [Sheet2$]";
// cmd.ExecuteNonQuery();
// Console.WriteLine("Drop Sheet2");
// cmd.CommandText = "DROP TABLE [Sheet3$]";
// cmd.ExecuteNonQuery();
// Console.WriteLine("Drop Sheet3");
cmd.CommandText = "CREATE TABLE froogle (JewelryType char(255), Category char(255), Metal char(255), itemNumber char(255), Title char(255), Description char(255), ourPrice float, vendorNumber char(255))";
cmd.ExecuteNonQuery();
Console.WriteLine("create table done");
// cmd.CommandText = "INSERT INTO froogle (JewelryType, Category, Metal, itemNumber, Title, Description, ourPrice, vendorNumber) VALUES " +
// "('JewelryType', 'Category', 'Metal', 'itemNumber', 'Title', 'Description', 'ourPrice', 'vendorNumber')";
// cmd.ExecuteNonQuery();
Console.WriteLine("Insert row one");
// int numrows = 0;
foreach(DataRow row in inventoryTable.Rows)
{
// Console.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7]);
cmd.CommandText = "INSERT INTO froogle "
+ "(JewelryType, Category, Metal, itemNumber, Title, Description, ourPrice, vendorNumber) "
+ "VALUES ("
+ "'" + row[0].ToString() + "', "
+ "'" + row[1].ToString() + "', "
+ "'" + row[2].ToString() + "', "
+ "'" + row[3].ToString() + "', "
+ "'" + row[4].ToString() + "', "
+ "'" + row[5].ToString() + "', "
+ "'" + row[6] + "', "
+ "'" + row[7].ToString() + "')";
cmd.ExecuteNonQuery();
// numrows++;
} // end of foreach
Console.WriteLine("the number of rows returned is {0}", inventoryTable.Rows.Count);
} // end of try
catch(Exception e)
{
Console.WriteLine(e);
}
finally
{
conMDB.Close();
Console.WriteLine("the connection to the my access database is now closed");
conxls.Close();
Console.WriteLine("the connection to the excel file is closed is now closed");
}
} // end of Main()
} // end of class
} // end of namespace
|
|
|
|
|
how can get case sensitive controll in ado.net access database?
i've tryed in this way but no case sensitive controll is made:
string query = "SELECT * FROM myTable WHERE Name = '" + textBoxName.Text + "' AND StrComp(Password, '" + textBoxPassword.Text + "', 0) = 0";
|
|
|
|
|
As you can see below I am calling various user definded functions. When I removed two of my functions and replaced them with inline sql I gained 6 seconds for my query execution.
<br />
Select * from tableName<br />
dbo.fn_GetParameterSamples(tbl_project.ID," ","0") as ParameterList, <br />
dbo.fn_GetTemplateSamples(tbl_project.ID," ","0") as TemplateList, <br />
(select count(ID) from tbl_samples where tbl_Project.ID = id) as sampleCount,<br />
(SELECT COUNT(id) from tbl_samples where tbl_samples.ProjectID=tbl_Project.ID and isHold != 0 ) as SampleHold,<br />
What I would like to do is replace my other user defined funtions with inline sql(please look below at the sql). I seem to be having some trouble doing this. I would like to add the following line but I keep getting an error(Error 141: A Select statement that assigns a value to a variable must not be combined with data-retrieval operations) is there a way around this error? I believe removing the user defined funtions could improve the performance of my query.
<br />
(Select @ReturnParam = ISNULL(@ReturnParam + ', ','') + tbl_template.type from tbl_samples,tbl_template <br />
where tbl_Samples.projectID = tbl_project.ID and tbl_Samples.ID = tbl_template.sampleID) as TemplateLIst<br />
<br />
|
|
|
|
|
Hi
how i can find out that a column is FK?
how i can find out that a column is PM?
FK:Foreign Key
PM:Primary Key
|
|
|
|
|
HI,
I have a question about using a temp table in stored procedure. If for exmaple two individuals are using my applicaton and both press a button that calls the same stored procedure at roughly the same time will SQL Server know how to handle the creation of a temp table and perhaps wait unitl the first called sp is done executing. Or do I have to deal with some type of concurency check when using temp tables.
Thanks, I appreciate any advice that can be given to me.
|
|
|
|
|
Books Online has a full discussion of the under CREATE TABLE. It has a section on Temporary Tables that explains about the # table name prefix.
#MyTempTable: Local temp table visible only within the current SQL session. This is the one you want to use if you have multple users creating temporary tables with the same name.
##MyTempTable: Global temp table that all can see.
|
|
|
|
|
Thanks Michael, Thats exactly the information I was looking for. What is books online? I went to www.bookOnline.com... I must be going to the wrong place anyhow thanks for the info.
|
|
|
|
|
Books Online is the Transact-SQL help that is found in the SQL Query Analyzer that comes with SQL Server.
You will not have it if you are using MSDE only.
|
|
|
|
|
DOWNLOAD Books Online HERE[^]
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
