|
Since Access is a file based DB, you can just copy it to a different folder:
<br />
<br />
'Gonna use FSO - need a reference to Windows Script Host object model<br />
Dim FSO as new FileSystemObject<br />
<br />
<br />
'Close any connections to the DB to ensure no access rights probs<br />
Conn.Close()<br />
<br />
'Use the FSO to copy the file<br />
FSO.CopyFile SrcMDB, DestMDB, bOverwrite<br />
<br />
Where SrcMDB is the DB to copy, DestMDB is the destination, and bOverwrite speaks for itself really - hope this helps
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Hello,
I noticed that when I add a new row to my DB (MS Access 2002 with VB.Net), the row is inserted to a specific place according to the primary key and not at the end of the table - my primary key is client's ID.
I use a form with datagrid control for searching a row, but after adding a new row, its location in the dataset is different from the location in the database (?!?!), this form returns the row number to the main form...but the row number in incorrect...
In the searching form I use DataView and also in the main form I use the default view which sorted by the ID.
the main form gets the row number from the searching form.
Another thing is that after rebuilding the DB, the row number is correct but that's not a good solution...
I need to understand if I did something wrong with inserting or using the dataview objects. and how do I get the real row's number.
//**************************
//after double-clicking the row on the datagrid, get the default row number
RowNum = dbDsDB.Tables("clients").DefaultView.Find(DtGrd.Item(hti.Row, 2))
If you need more info, let me know - I'm stuck
thanks, Roy.
|
|
|
|
|
Although MS Access gives you a lot of flexibility, you really should not be departing so far from DDBMS principles.
-- Never rely upon storage order. You should consider that a black box and never be concerned with or rely on the order in which the rows are stored. There has never been a guarantee that the data will be stored in the order it was received. Therefore, don't use RowId!
You should use Primary Keys & Foreign Keys (referential integrity) to join tables and to match tables to forms. If enter order is important for you application, I suggest you add a time stamp attribute to your rows and reference it in your ORDER BY clause.
|
|
|
|
|
Hey Michael,
I guess I did make myself clear, I don't need the row's actual position in the DB.
I have thid code:
RowNum = dbDsDB.Tables("clients").DefaultView.Find(DtGrd.Item(hti.Row, 2))
the: DtGrd.Item(hti.Row, 2) is the current row in the datagrid(column 2).
(this is in the searching form that contains datagrid)
So, after this line of code, I suppose to get the row number in the default view:
Me.BindingContext(dbDsDB, "clients").Position = frmSrch.RowNum
(this is in the main form that calls the frmSrch).
But I won't seem to work and I get other row instead 
..
Any Ideas??
thank Roy
|
|
|
|
|
I am writing a stored procedure where the WHERE clause may contain 100's of conditionals (i.e. UserID=1 OR UserID=2, etc). The field being searched will always be the same. It is just the number records to be returned I won't about. Is there anyways to pass the stored procedure an array or something similar to specify the records to return? Writing a statement that includes 100's of "UserID=1 OR UserID=2 OR..." breaks my layer abstraction so I'd rather just send the list of User ID's I want to get. Is there any way to do something like this?
|
|
|
|
|
Can you pass a comma-delimited string of values as a single varchar parameter? Then use that parameter as the list in an IN(...) operand?
|
|
|
|
|
You can try passing a string with the user id's and use it in the WHERE clause of your statement,
e.g.
SELECT *
FROM tblUsers
WHERE UserID IN (SELECT [Value] FROM dbo.Split(<code>@ListOfUserIDs</code>,','))
The code above uses the user defined function Split, which splits a string by the delimiter and converts it into a table.
Below is the user defined function that you can modify as needed.
CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue (value)
SELECT
Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1)))
Set @List = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List))
END
INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))
RETURN
END
There is another way using the EXEC statement in your stored procedure to execute the SQL, and you don't need the split function for this. Some users argued about the efficiency and safety of using EXEC statement, but it's up to you to decide.
DECLARE SqlStr varchar(1000)
SET SqlStr = 'SELECT * FROM tblUser WHERE UserID IN (' + @ListOfUserID + ')'
EXEC (SqlStr)
Both code will work even if you only pass one ID.
I hope it helps  .
|
|
|
|
|
Thanks for the function. It works great.
|
|
|
|
|
Hello all .....
i m getting a Sql Error "Log file is not available"............when i m taking the backup....its both mdf and ldf are in its predefined location.....
let me know if any solution.....
******
|
|
|
|
|
Hi everyone,
I am using Access to compare records between a SQL Server database instance and Oracle. I have the tables linked and most of the comparisons work fine. I am using joins on the table and comparing each column.
However, I have columns that have Memo fields and Access does not let me compare these fields. Also, it does not do any comparisons between the date fields.
Is there a way to compare these MEMO and date fields using some VB script. I have not used VB scripts much and would appreciate some help.
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Hi,
I'm just wondering if it's possible to create a new table in a database based on a programatically generated DataTable without having to do it by hand?
thanks,
matt
|
|
|
|
|
If I understand you correctly - No.
|
|
|
|
|
lol dagnammit i didn't think so but thought i'd check.
|
|
|
|
|
Hi,
I created a dataset with 2 tables and a relation in a module.(VB.NET)
Now, I have a form with datagrid that suppose to show only the parent table.
How can I hide the symbol (+) from the parent datagrid without deleting the relation.
Thanks
Roy.
|
|
|
|
|
hi to all
How to connect Oracle Lite Database with ADO.NET in VB.Net for Smart Devaice Applications if anybody know that then plz. inform me.
thanks in advance.
Good bye
Manoj Mevada
|
|
|
|
|
Hi,
my query is given below.
"select dp.detail from procdure p, mentryi m, dtentryp dp
where m.master=p.proc
and dp.master =m.detail
and m.item=481"
when i am running the query its giving me the output, that is fine. now using ado.net DataReader, how do i read individual columns from different tables. I am using DataReader b'coz, I am reading a BLOB field from mysql database and converting into .jpg images, that is also working fine, if i am reading from just one table.
I need to read specified columns from different tables in the same DataReader. if i am using the instance like "p" for procdure table, it is giving me error and its not identifying the variables associated with the table.
Any help would be appreciated..
vijay
|
|
|
|
|
u can acess any column by this
dr.getstring(x)
where dr is datareader object
getstring is data type column
and x index of column which starts from 0 for the first column;P
|
|
|
|
|
I need to create a query where I pass it 'Person' and I create a column called 'There'. I'm using SQL Server 2000.
------------------
Table X
------------------
ID Desc
------------------
1 Glasses
2 Red Hair
3 Blue Eyes
-----------
Table Y
-----------
Person ID
-----------
856 1
856 3
900 1
900 2
900 3
-------------------------------
Needed Result when I pass '856'
-------------------------------
Desc There
-----------------------
Glasses True
Red Hair False
Blue Eyes True
This means that I must always output every possible 'Desc' and set 'There' to 'True' when we have a match otherwise set to 'False'.
Thank You in advance for your help!
|
|
|
|
|
This should do it:
SELECT x.Desc, case when y.ID is null then 'False' else 'True' end as There
FROM X
LEFT OUTER JOIN Y ON Y.ID = X.ID
WHERE Y.Person = 856
I hope I didn't just do your homework assignment for you!
my blog
|
|
|
|
|
hehe, I guess it does look like an assignment I would have had. Actually it's a huge complex query that I simplified as far as I could for the sake of getting a response. I still need to translate the case statement into the full version, then I'll see how well it goes.
Thanks!
|
|
|
|
|
Still can't quite get it. Here's more detail.
tTesterUsage Table
------------------
TesterID | UsageID
------------------
856 | 97
856 | 98
900 | 97
900 | 102
tLuUsage Table
--------------------
UsageID | UsageIndex
--------------------
97 | 60001
98 | 60002
99 | 60003
100 | 60004
101 | 60005
102 | 60006
tUConsumer Table
---------------------------
CMCode | GeneralDescription
---------------------------
60001 | Reads Books
60002 | Reads Magazines
60003 | Reads Newspaper
60004 | Watches TV
60005 | Rents Movies
60006 | Movie Theatre
******************************************
* tLuUsage.UsageIndex = tUConsumer.CMCode*
******************************************
Desired Result Below when TesterID = '856':
-----------------------------
GeneralDescription | There
-----------------------------
Reads Books | True
Reads Magazines | True
Reads NewsPaper | False
Watches TV | False
Rents Movies | False
Movie Theatre | False
I've tried this below, still doesn't quite do it.
CREATE PROCEDURE usp_tp_GetConsUsage
@TesterID char(7)
AS
declare @Out table (There varchar(5), TesterID char(7))
insert into @Out
SELECT tUConsumer.CMCode as There, tTesterUsage.TesterID
FROM tTesterUsage inner JOIN
tLuUsage ON tTesterUsage.UsageID = tLuUsage.UsageID inner JOIN
tUConsumer ON tLuUsage.UsageIndex = tUConsumer.CMCode
where tTesterUsage.TesterID = @TesterID
SELECT distinct tUConsumer.GeneralDescription,
case when Gonz.There is null then 'False' else 'True' end as There
FROM tLuUsage inner JOIN
tUConsumer ON tLuUsage.UsageIndex = tUConsumer.CMCode inner join
tTesterUsage on tLuUsage.UsageID = tTesterUsage.UsageID left join
@Out Gonz on tTesterUsage.TesterID = Gonz.TesterID
GO
|
|
|
|
|
tUConsumer needs to be the main table o the query. Something like this:
SELECT distinct c.GeneralDescription,
case when l.UsageId is null then 'False' else 'True' end as There
FROM tUConsumer c
LEFT OUTER JOIN tLuUsage l ON c.CMCode = l.UsageIndex
INNER JOIN tTesterUsage t ON l.UsageId = t.UsageId
where t.TesterId = @TesterId
my blog
|
|
|
|
|
When I try it that way it only displays the GeneralDescriptions that apply to that TesterID. In other words, it only displays what is True. I need all the possible GeneralDescriptions displayed, with either False or True next to the description.
Thank You Again for all of your help
|
|
|
|
|
Ok, it's worked out now. Thanks for all the extended help!
|
|
|
|
|
Hope the following query would help
select distinct UC.GeneralDescription GeneralDescription, case when TU.TesterID is null then 'False'
else 'True' end As There from tUConsumer UC
inner join tLuUsage LU on UC.CMCode = LU.UsageIndex
left join tTesterUsage TU on TU.UsageID = LU.UsageID
and TU.TesterID = @testerID
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
