|
Our app gets info from a product database (MDB), and we have a database editor tool included in our package. Our thought there was to provide users with a way to maintain their DBs without having to have Office installed.
So far, so fine-by-us. However, some of our users want to be able to update their DBs from data in Excel (though if they have Excel, they should have access to Access no? And they could just do this directly? Anyway. Whatever.)
So, for the very small percentage of our users who are interested, I've added a Query window to our database editor app. It allows users to run SELECT, DELETE, UPDATE and INSERT queries (Yes I know the risks! Yes, some operations are transactionally protected!).
Behind my query window / data-bound grid is a CDaoRecordset (yes, I know it's old, and deprecated, but it works, and extricating DAO from the app is a lot more work than I have time for at the moment).
So the question is, can I write a query in my query window that can update / insert to my component table from an Excel source?
I'm thinking something along the lines of:
SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])
But executing something like that, the app complains about an unrecognised FROM clause.
Any thoughts / suggestions are much appreciated.
|
|
|
|
|
Hi,
It's been years since I last used DAO but few things come in mind:
- The statement executed using CDaoRecordSet is (if I recall correctly) parsed by the provider. This actually means that your DAO provider should be able to understand the SQL syntax you use, but that's not possible (since you're using T-SQL syntax)
- Unfortunately CDaoRecordSet doesn't have the capability to pass the statement to the server as-it-is
- You could try to use CDaoQueryDef instead and use the passthrough option so that the provider won't interfere. After all you're not trying to get any results back to the client but to populate a table in the database
Also keep in mind that in your statement you're referring to a file which resides on the database server. Meaning that the file xltest.xls must reside in directory C:\test at the database server.
|
|
|
|
|
I created dataset in Visual Studio 2008 using payroll_2007_08 database with payroll_2007_08connectionstring (sql server 2005), now recently we changed the database name to only payroll. If i remove the dataset all the fields which i created in report will automatically goes because of this connection string removed.
How to change the payroll_2007_08connectionstring to payrollconnectionstring (dataset) without removing the fields in reports.
If anybody knows, please reply me. Thanks in advance.
|
|
|
|
|
Hello Everybody,
Is it possible to Rollback Table in SQL Server 2005 if it is Truncated?
Thanks
If you can think then I Can.
|
|
|
|
|
No: see here: TRUNCATE TABLE (Transact-SQL)[^].
From the page:
"The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log."
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Hi,
If you mean that can you rollback the truncation of a table if it's done in a tranaction: Yes.
You can test this with a small test case:
CREATE TABLE TruncTest (
Column1 int);
BEGIN
DECLARE @cnt INT;
SET @cnt = 0;
WHILE @cnt < 100 BEGIN
INSERT INTO TruncTest VALUES (@cnt);
SET @cnt = @cnt + 1;
END;
END;
SELECT COUNT(*) AS Rows FROM TruncTest;
BEGIN TRANSACTION;
TRUNCATE TABLE TruncTest;
SELECT COUNT(*) AS Rows FROM TruncTest;
ROLLBACK;
SELECT COUNT(*) AS Rows FROM TruncTest;
|
|
|
|
|
Dear All,
Usage: access 2007, VBA
I have 2 comboboxes, cbo1 and cbo2 , cbo1 is loaded with numbers from the database, and cbo2 is loaded with text from the database.
dim cb1 as double
dim cb2 as string
cb1 = CDbl(me.cbo1.value) ' i chossed: 800524
cb2 = CStr (me.cbo2.value) ' i choosed: Hello World
msgbox cb1
msgbox cb2
Result : 800524
Result : 4
where 4 represent the location of the "Hello world" , fourth element in the cb2, while i need to get the text 
I can get wat i need from this result (using dataset , and query) but it cost code lines and memory resource, shouldent .Value returns the exact value that is selected ???
0 will always beats the 1.
|
|
|
|
|
cb2 = CStr (me.cbo2.text)
|
|
|
|
|
Hi,
I am planning to choose an embedded database for my windows application. If I choose MS Access, will my user need Microsoft Office installed in his/her machine in order to use my application ? Can I embed the required libraries so that my application will work without MS Office ?
|
|
|
|
|
You can develop your application in VB.NET or C#.NET and your clients will not need MS-Office installed for your application to run.
You will be delivering an executable (*.exe) and a (*.MDB) file to your customer, all of your compiled code will contain the libraries necessary to read/write data to the MS-Access MDB database.
|
|
|
|
|
Thank you so much. Just one more question. Do you think MS Access is a good choice over SQL Server Compact ? I mean, performance and portability wise.
|
|
|
|
|
MS-Access vs SQL-Server
Depends on the nature of your application.
For example, I built a very small membership application which stores Names, Addresses & Photos of people beloning to a swim club with MS-Access as the data storage. The program is not networked and is very small; less than 300 members. So it works fine.
If your application is going to be used by multiple users over a network, then I would strongly suggest using MS-SQL.
Good luck.
|
|
|
|
|
Nadia Monalisa wrote: Do you think MS Access is a good choice over SQL Server Compact ?
Not really. If you don't plan on using Access itself, then I see no advantage. SQL Server, even the Express version (which is free), seems like the more capable and reliable component. And MDB stuff is bound to cause trouble in a 64-bit environment, so it will not be the long-term way to choose.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Hi, Thanks for the reply,
Full blown SQL Server or SQL Server Express cannot be used for my application as my user wont install those. I have only 2 options,
1. SQL Server COMPACT OR
2. MS Access
So, by performance, are you suggesting me to use SQL Server COMPACT ?
|
|
|
|
|
I can't answer that. I suggest you perform a little experiment, just create some code simulating your typical DB operations, fit the right DAL, and observe on a relevantly sized but otherwise fake database.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Nadia Monalisa wrote: So, by performance, are you suggesting me to use SQL Server COMPACT ?
I suggest that you measure it's performance, as different machines will display different behaviors. In general, it seems to be suggested that Sql Ce is the faster of the two, with my own testresults[^] listed below;
Microsoft Access (Provider: OleDB)
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database1.mdb;User Id=admin;Password=;
16377 records 54 seconds, averaging 0.0033 seconds per inserted record.
SQL CE (Provider: SqlCe)
Data Source=C:\MyDatabase1.sdf;
16377 records in 38 seconds, averaging 0.023 seconds per inserted record. Sql Compact does have other limitations, so it's a trade-off between the extra speed, or extra convenience. You can't use stored procedures for one, while Access has the benefit that you can even embed some reports in the datastore.
Don't take a suggestion based on rumors on the internet - make a list of arguments that prove that it's going to be the better choice for your specific scenario.
I are Troll 
|
|
|
|
|
Nadia Monalisa wrote: I mean, performance and portability wise.
On average performance is unlikely to be a concern for a single user system. With specific information about the functional needs of your application it might be more relevant.
Reliability on the other hand could be a factor.
Portability is probably meaningless because anything that is going to run .Net is going to be able to run SQL Server.
Presumably there is no intention at all, under no circumstances, where more than one user of the database is required. If so then SQL Server is probably a better choice.
Installation can be made a non-issue by providing an installer that installs everything.
|
|
|
|
|
|
Has anybody ever seen anything like this in the WHERE clause of an SQL SELECT?
WHERE a.somenumericvalue = + @somenumericvalue
I certainly haven't and I don't know what it does. It's kind of difficult to Google for as well so that's why I'm putting it up here. Anyone got any ideas?
|
|
|
|
|
Usually the + sign will have braces around it and appear following the field. Or at least that is how I'm familiar with it. It is for doing an open join on table. Or as I like to think, anything matches on NULL.
where a.ID_Field = b.ID_Field (+)
All rows from table a will be returned.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Yes, I've seen that old join syntax before. It's not what's going on here though. Most strange. I can't even see that it's affecting the logic.
|
|
|
|
|
Would it have anything to do with the bind variable?
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I can't see how. All this code was ripped out of a ginormous Python framework last year; I'm new on the site. It's possible that this is a concatenation that slipped through, and SQL Server doesn't give a compile error, possibly because it's applying a plus to the bind. Or maybe this is a jury-rigged ABS()? I dunno, but it's queer as folk. I'll ask around tomorrow morning...
|
|
|
|
|
Run the following.
Then replace the '-' with a '+'.
declare @i1 int;
declare @i2 int;
set @i1=1;
set @i2= - @i1;
select @i2;
|
|
|
|
|
Hi
I'm getting error while retrieving only a dataset <100k rows (~150MB estimate) - ran into this error: "Description: Not enough storage is available to complete this operation."
After googling seems like it's related to memory (As supposed to disk space or SQL server)
This is how I instantiate connection and command:
ADOconn.conn = actxserver('ADODB.Connection');
ADOconn.conn.ConnectionString = connStr;
ADOconn.conn.CursorLocation = 2;
ADOconn.conn.ConnectionTimeout = xxx;
ADOconn.conn.CommandTimeout = xxx;
ADOconn.conn.Open;
ADOconn.command = actxserver('ADODB.Command');
ADOconn.command.CommandTimeout = 0;
ADOconn.command.CommandType = 4;
ADOconn.command.ActiveConnection = ADOconn.conn;
Any idea if there's a property on connection or command whereby max memory limit can be set? (And is there a default?)
Thanks
ADO Connection object http://msdn.microsoft.com/en-us/library/ms807027.aspx
ADO Command object http://msdn.microsoft.com/en-us/library/ms675022(v=vs.85).aspx
Microsoft SQL Server connection string http://www.connectionstrings.com/sql-server-2005
dev
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
