Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Database Table Locking In SQL Server


Hi, I'm writing a program that use RDO connecting to SQL Server 6.5. I found that some of the tables were locked when the program was still running. I then step traced my program. After the BeginTransaction statement, I wrote a SQL statement to drop a database table in SQL server. It's found that some of the tables were locked after excuting this statment. Since the transaction was not commited yet, only that table being deleted should be locked. However, this was not the case. Well, I already have closed the recordset and set it to Nothing before going to other procedure. Also, SQL Server login do has the Drop permissions on that table. However, still some of the tables are locked. Beside, same thing happened when creating table instead of dropping table. I'm looking forward to getting help. Thx




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Locking MS-SQL Server Database
Hi All,

I can lock a access database with a password by making an mde file but how to lock MS-SQL database.I don't want the user to see the structure of the database.How do i do this?.Help me on this.Thankz in advance.

Possible To Set Server, Database And Table At Runtime In SQL?
Is is possible to set the servername, database and table at runtime and then do a select using that setting? In other words, something like:

declare @r varchar
set @r = "server.databasename.dbo.table"
select * from @r

This needs to be all inside a query, not in VB...
Thanks!!!

About SQL Server 2000 Database Table
I used "DTS Import and Export Wizard" to convert Access Database into SQL Server 2000 Database.
and then I tried to open the converted Database in SQL Query Analyzer, then I could see the scripts of creating Table, here is one bit of
code to create the table called "Form"

Code:CREATE TABLE [Form] (
    [ID] [int] NOT NULL ,
    [LevelID] [int] NULL ,
    [FormName] [nvarchar] (30) NULL ,
    [Filename] [ntext] NULL ,
    [DateUpdated] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
But I don't understand what is "ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]" means?

anybody could explain it please? I am especially confused with the " TEXTIMAGE_ON"

Know List Database And Table In SQL Server
I use this macro to list in A column of sheet the name of sql server on my lan.
Is possible to "scan server by server" and insert into column B the name of database and related table?
Tks.
Sub test_sql()
Dim TEST As String
Dim RIGA As String

Set sqlApp = CreateObject("SQLDMO.Application")

Set serverList = sqlApp.ListAvailableSQLServers

numServers = serverList.Count
RIGA = 2

For I = 1 To numServers

TEST = serverList(I)

Range("A" + RIGA) = TEST
RIGA = RIGA + 1
Next

Set sqlApp = Nothing

End Sub

Sal21

How To Export MS Access Table To An Ftp Server Database
Basically, after the user updates the database on her local machine, I need to transfer the entire Products table from her database to the access databaase located on the web server. I've looked at the TransferDatabase method but can't get it to log in to the ftp server and replace the products table. Any help would be greatly appreciated! Thanks,

Robert Evans
-developer

How Can We Use SQL Server Database Table Index In VB Code ?
Hi Guys,

I have the following problem to code

How can we use SQL server database table index in VB code ?

I could not find in recordset properties to set the index.

I am using SQL Server 2000 and VB6

I just want to use table in the query but I want to user specified index to that because that table is have three index.

Thanks

Kamlesh Gujarathi.

kvgujrathi@yahoo.co.in

kamlesh.gujarathi@itshastra.com

MsSql Server-Database Problem.Table Not Opening Help Please !
Hai ,

I hope you are familiar with Ms Sql server.
I am having a problem in the Server.
I tried some query in the query analyzer and tried to create some procedure and execute from VB

after some time I open the tables to view the fields,

the following error is showing, all database has no proper backup and it is very impartant. Kindly tell me if you know how to solve this problem and view the tables. Not only this problem occurs for the Table and Views in my Database, it also happens for the default Database in the Server such as Pubs and NorthWind, When I try to open any Table or View the same error message is showing. i hope the datas are safe because one of my front end application is running here. Kindly reply me as soon as possible.


An unexpected error happened during this operation
[Query] Query Designer encountered a Query error :
Unexpected error.



Thank you,
Chock.
itchocks@rediffmail.com

[b]How Do Load Data Files Into MS SQL Server 2000 Database Table?[/b]
Hi:

I am trying to load some text data from a folder into MS SQL server 2000 database table using a procedure I have created in SQL query analyser. The procedure prompts you to supply a file name and copies the data using bcp format into the table. This works okay with one just file. However, there are many data files in the folder and I would like to load all of them at once (one after the other). How do I do this? Could someone help me, please. Here is my code:

Code: create procedure LeicsIPdata @filename varchar(50) as
declare @import varchar(50)
declare @counter int

select @import = 'dir f:load
wcsleicsapc' + @filename + '.DAT'
exec @counter = master..xp_cmdshell @import, no_output

if (@counter <> 0)
    begin
        print ('counting ...')
        print ('----------------------------')
        print ('cannot find file' + @filename + '.DAT in f:load
wcsleicsapc')
        print ('----------------------------')
        print ('ensure that the files are located in the above directory')
        print ('----------------------------')
    end
else
    begin
        truncate table hnatemp..ac_ip_leicsite
        print('master..xp_cmdshell' + ' ''' + 'bcp hnatemp..ac_ip_leicsite in f:load
wcsleicsapc' + @filename + '.DAT /ff:cpfmtip_data.fmt /U /P' + '''')
        exec('exec master..xp_cmdshell' + ' ''' + 'bcp hnatemp..ac_ip_leicsite in f:load
wcsleicsapc' + @filename + '.DAT /ff:cpfmtip_data.fmt /U /P' + '''')
    end


Thank you in advance for your help.

Ray Abak-Lenga.

Insert Data Into A Row In A Database Table In SQL Server With Visual Basic
I figured out the how to retrieve data from SQL, but I have not managed to add data to a table.

Here's what I've got so far. I would appreciate it greatly if somebody could fill in the blanks that would make this procedure work and insert the data.

Public Sub AddToDatabase()
    Set RCconn = New adodb.Connection
    Set TuneCMD = New adodb.Command
    ' Establish Recordset
    Set Results = New adodb.Recordset
    
    'Establish a Connection
    With RCconn
       .Provider = "SQLOLEDB"
       .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Tuneman"
    End With

    'Open the connection
    RCconn.Open
    
    Results.CursorType = adOpenStatic
    Results.CursorLocation = adUseClient
    
    Results.Source = "INSERT INTO OrderInfo VALUES('" & frmAddMusicData.txtBand.Text & "')"
    Set Results.ActiveConnection = RCconn
 
    Set Results = Nothing
    RCconn.Close
    Set RCconn = Nothing
End Sub

Help In Table Locking
I have a table in SQL Server 2000 which is frequently used by the users. Say 3-4 users will be doing some kind of insert or update on the table at any given point of time. I want to run a certain stored procedure on that table triggered by the user.

The stored procedure takes some time to run, say 50-60 seconds to complete. I need that when someone run the stored procedure, no one should be able to do any changes to the table during that time. how can i do this? i think i have to use table lock kind of thing. Is there any better way? How do we achieve Table Lock in ADO and in SQL Server Stored Procedure.

Thanks in advance

Locking A Table
Dear Respondent(s)

I've recentrly developed an application.

In this application a particular Id_No is generated based on the last value in the table..

How do I lock the table so that there are no duplicate id_no
in the table.... ?

I don't want to keep any AutoNumber field.... It should be as per my requirement (say prefix "HP").....


Regards
Kunal Singh

Locking A Table From VB, Please Help Me!!!
How can I lock a Table from VB, to avoid create duplicate records in a Table. And then leaved it unlocked.
Help me, please !!!!
Thanks

Table Locking !!
hello,

i have made a multi user application in which one person is using a
program where some of the table gets lockd because of BEGIN TRANSACTION
being used there ...........another person when he access that same
table it gives a error messag after very long that table is locked...how
can i check the status of the table whether the table is lockd by some
user or not ?

is there any way to check the lock status of the table ?



Regards



Neeraj Vasudeva

Need Help To Avoid TABLE LOCKING
Hi Folks,
I need help with something that's been driving me crazy.

I need a very simple read-only connection to a FoxPro table in VB. Ideally it should have no lock atall (ie be transparent)

(Regardless of that) I absolutely must free up the table when I've finished with it.

Not only cant I free up the table but it doesnt even free it when I stop the program. I have to physically close VB down to free up the table lock!!

Crazy crazy


Code:

Dim moConn As ADODB.Connection
Dim recRefresh As ADODB.Recordset

Set moConn = New ADODB.Connection
moConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
"SourceType=DBF;" & _
"SourceDB=WNAME.DBF" & ";" & _
"Exclusive=No"

Set recRefresh = New ADODB.Recordset
recRefresh.Open "select * from 'WNAME.DBF', moConn, adOpenForwardOnly, adLockReadOnly, adCmdText

.
.
.
recRefresh.Close
moConn.Close
Set moConn = Nothing
Set recRefresh = Nothing





Any and all suggestions very welcome??

ADO Table Locking Problem
Hi Folks

When I open a FoxPro table I seem to lock it and keep it locked. Close doesnt work, setting the connection to Nothing doesnt work. Stopping the code doesnt work! Only shutting VB down completely free's up the Table again.

(I test if its locked by trying to directly open it in FoxPro)

All I want is a read only, forward only look at some data then drop the connection completely.


Code:
Dim moConn As ADODB.Connection
Set moConn = New ADODB.Connection
moConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
"SourceType=DBF;" & _
"SourceDB=CUSTOMER.DBF;" & _
"Exclusive=No"
.
.
.

Set recRefresh = New ADODB.Recordset
recRefresh.Open lsSQL, moConn, adOpenForwardOnly, adLockReadOnly, adCmdText


I'd really appreciate any help you can offer.

Many Thanks
Raz

Problem In Locking A Table.
Hi,

I'm looking for a row-wise locking machanism which not only lock the row of
the table for insert/update/delete but also for reading. That means i would
like to have something which does not allow other users to even read the row
untill the transaction is not completed by other user who started the
transaction.

Looking forward for quick response.

Best Regards,
Manish

Locking A Table During INSERT
I have a situation where there is an INSERT into a table, in which it
may take 1-2 seconds (lots of data in a loop)...I included the code, in
case someone has a better suggestion. (I know this is probably not the
best way to do it) I'm grabbing a big recordset from an AS/400, then
looping through it, inserting each row into a SQL 2000 table.

During this time, there are other processes that access data in the SQL
table - I want to prevent access to this table during the INSERT, so
should I use some kind of locking??? Below is my code, which locking
should I use?? I delete the entire table before the insert.

Thanks for any help!

Dim conn As New ADODB.Connection
Dim cnx As New ADODB.Connection
Dim rs As New ADODB.Recordset

conn.Open "Provider=IBMDA400;Password=;User ID=;Data Source=;Transport
Product=Client Access;SSL=DEFAULT"
cnx.Open "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User
ID=;Initial Catalog=beds;Data Source="
rs.Open "SELECT RNRST, ROOM#, RBED#, REXT#, RSTS, RSEX, RACCT, RPNME,
RAGEQ, RVIPL, RISLN, RUSR6, RUSR7, RUSRX from PH#FILE.PHPRMBD where
RRSTS1 = 'A'", conn
cnx.Open

cnx.BeginTrans
cnx.Execute ("DELETE from beds")

Do While Not rs.EOF

sq = "INSERT into beds values('" & rs("RNRST") & "','" & rs("ROOM#")
& "','" & rs("RBED#") & "','" & rs("REXT#") & "','" & rs("RSTS") & "','"
& rs("RSEX") & "','" & rs("RACCT") & "','" & Replace(rs("RPNME"), "'",
"") & "'," & rs("RAGEQ") & ",'" & rs("RVIPL") & "','" & rs("RISLN") &
"','" & rs("RUSR6") & "','" & rs("RUSR7") & "','" & rs("RUSRX") & "')"
cnx.Execute (sq)

rs.MoveNext
Loop
cnx.CommitTrans

rs.Close
conn.Close
cnx.Close

Table Locking Problem
Hello Prasad,
Thankx to reply,

Actualy, some time when user click for report (report created in crystal report 8.5)
and after some time when we entered in ms sql enterprise manager->management->current activity-> locks/process id then we got the block some ID and we are unable to find the solution for this blocking, so please provide me some possible help,
than you

Locking Records In A Table
hello all,
can any one please suggest me how to lock records in a table . I am using oracle as backend.
my problem is.....
when i am locking using optimistic locking oracle
is taking time to respond if there is any concurrent access on the same record.
I would be greatful if any gives me a solution for immediate reponse of a concurent access on the record that is locked.

Locking In Sql Server
i'm using vb.net.. n i'm new to sql server.. i want lock my table when the 2nd user access to my program n trying to modify the table which i;m also using it.. where should i put my coding n wat should i code??

Locking With ADO.NET And Sql Server
Hi,

How can we implement pessimistic approch with VB.NET? (using SQL Server)

If I make a transaction I can use trasaction isolation level, but if I just make a commad? What type of lock will be made?

And if i use transaction level, Am I always using pessimistic approch?

Thank you



Locking A Table In The Stored Procedure
Hi,


I want to find out about locking a table. In a stored procedure, First we are selecting a maximum number and incrementing it and inserting into the table. The stored procedure is called many times in a minute. it is inserting the same number more than 1 time. I think this is happening because, before insert statement is running, the stored procedure is called again. I am trying to lock the table after select statement and release it only after the insert statement. Can anyone tell me where i can find the code for locks.

Thank you,

Frustrating Table Locking:: MS Access
An awkward situation has arisen for one of my large (financial) clients... After delivering many multi-users MS Access databases over the years, the current dbase I have built generates an internal MS Access "system" error message, stating that the user can either
[Drop changes, Copy to clipboard, or Save changes].

- this message occurs when two or more users are accessing the same table in Access 2000, ie, one user goes into a form and makes updates to address or name details, then the next user goes into the same screen (say, 20 seconds later) and then that user will usually get the error msg mentioned above.

Never before has this message appeared in multi-user access dbases I have written, and my client thinks its a fundamental flaw with MS Access. I produced a sample database to replicate the problem - get 2 users to enter their initials, "BG", and then press a "Submit" button - (this writes the initials to a table --- the table that both users are writing to when they press the button) --- this test has had no problems at all in the past (late 90's) but causes the abovementioned message above.

Any advice in changing the recordsource of the form is not applicable, etc, and I have actually solved the symptom by Quitting them from the app. on the Form_Error event. If this was not built in, and if they pressed Save changes, the database would crash completely, and not work until I removed the corrupted table, etc.

The client is using a Novell network, and I have been told that novell and MS dont co-operate all that well.

Any ideas about this write conflict error message, or will I have to get on the backs of the system admin people who have no idea what is going on..

Regards, Trans.

Re: Row Level Locking Of Records In A Table
I have developed a Software with vb6 along with sql server as back end. The Software is a Multi user system. I have two tables, of which, the first table is the Item master table where i store the details of various items. The schema of the table is like

ItemCode Text Unique
ItemName Text

The second table is used to fill out the order, and the sample schema of the table is like

Order_No
ItemCode
Quantity
Rate

When one user was viewing or accessing an item in the Order Form, another user changed the name of the item from the Item Master form. When the Report was viewed the item was not the same as that ordered.

Is there any solution to avoid this problem






Edited by - avis_sivakumar on 6/14/2004 11:25:43 PM

BeginTrans - Update Is Locking My Table
Hello

I have a problem when the connection is in Transaction and smeone calls update on a table then the table is locked until the trans is commited. Is there any way around this ?
or is it not possible when in Transactions ?

Thanks

Record Locking With Sql Server
I'm trying to lock a record for subsequent updating. By locking I want another instance of the application to be blocked from viewing the same record.

My questions:
1. Are there any properties / parameters that need to be set with Sql Server.

2. What VB syntax is necessary to invoke the locking.

I've tried a couple of methods Any comments, url's, sample syntax is appreciated. Thank you.

SQL Server Record Locking
Hi there,

I am in a conversion of MS Access to SQL server.
Everything is fine when I use MS Access.

I use commit control. When I use SQL server I want to insert and change records. Records get locked after an insert or update when I want to read the same records. The commit had not taken place.

I am not that familiar with SQL server, but before the commit I am not able to open the table with: open the open table --> return all rows. When I used MS Access I was able to open the table.

I use the following connection string:
SERVER = "TEST"
DATABASE = TESTDB
connectie = "Provider=SQLOLEDB;Server=" & SERVER & ";Database=" & DATABASE & ";Integrated Security=SSPI;Persist Security Info=False;"

Does anybody know how to solve my locking problem?


Many thanx!!!

SQL Server And Row-level Locking!!!
Hi!

I am developing an SQL Server database. This is my first experience with writing client-server application. If I try to use a database grid from Ms Data Grid to any other such as GridEx, I get an error when two users try to edit the record simultaneously.
What is the best approach - wrapping all database calls with BeginTrans and CommitTrans in the ADO connection code and using INSERT, UPDATE, DELETE and SELECT procedures, or using standard controls such as Db Grid, etc?

Thanks a lot in advance! I really need these tips because of all the books I have on SQL Server 2000 there's none that can answer questions like this.

Thanks,
Stas

Reading A Foxpro Table From Excel Without Locking The Foxpro Table
Hi,
I need to read a foxpro table from excel.
I've made it by different ways but have problems:

- with a querytable, through ODBC connection.
Problem: The query results are put into an excel file, and processing it becomes very slow. Also the user sees all of the database information.

- with opendatabase, and openrecordset(sql select).
Problem: The table is locked while processing the information.

I just need to make a report, so I don't need to lock the table. As a matter of fact, I CAN'T lock the table, since it might be used by another program.
I know it's locked because after the instruction openrecordset, when I try to open the table directly from excel, it says the file is locked, and can be opened in read mode only.

My question is:
What would be the way to have the records without locking the table, and without putting them in an excel sheet?

Please, help!!! I've been trying a lot and don't know what else to do.

AMOTA

How To Move A Record From One Database Table To Another Database Table In Visual Basic Us
Hi
 
   plz help me

  How to move a record from one database table to another database table in visual basic using MSAccess Database
  using ADODB Record set.


  Datbase A from table a
           to
  Database B to table b.

   

Thanks


Yours

mk_mur

Locking Windows NT4 Server Workstation
hi
can some one please tell me if it's possible to lock a NT4 SERVER WORKSTATION?

i searched the forums and internet and found nothing.

i know that the API only works on 2k upwards.

some one built it hopefully one of you can un build it. .

*just had another look on the net, found a program that ran the screen saver with the lockworkstation function. but it had a bug it just kept locking the workstation and i couldnt log in again.
any one have any code that could run the sreensaver in locked mode?

Page Locking SQL Server 2000
Hi there,

I still have a locking problem.
I use VB 6.0 and SQL server 2000 SP2
After I insert or update a record under commit control page-locking happens.
First I thought the complete table got locked, but when the table has enough records I was able to open the table.
I read on the forum that SQL server 2000 uses record locking, but in my case it used page locking.


I use the following code:

connectie = "Provider=SQLOLEDB;Server=" & SERVER & ";Database=" & DATABASE & ";Integrated Security=SSPI;Persist Security Info=False;"
RSDDatabase.ConnectionString = connectie
RSDDatabase.Open

RSDDatabase.BeginTrans

SQL = "INSERT INTO AUFN00PF Values(1,2,3,4,5)"

AUFN00PF.Open "AUFN00PF", RSDDatabase, adOpenKeyset, adLockOptimistic
RSDDatabase.Execute SQL

AUFN00PF.Close

----> PAGE LOCKING APEARS, NOT RECORD LOCKING

RSDDatabase.CommitTrans


Does anybody have any idea how I can disable page-locking under SQL server 2000.

Many thanks in advance !!

Is There Any Facility Available For Locking The Stored Procedures In Sql Server
Hai Gurus,
Is there any facility available for Locking the Stored Procedures in Sql server ??

Jet Database Locking Up
With multiple users, the Access 97 database accessed through Jet locks
up at seemingly strange times, with an "admin has control" error
message. I don't have any active writes to the database going, and the
lock seems to free up after a few minutes, but it's frustrating. Any
ideas?

-Owen

Locking Database
This is how I'm using lockedits property. See what it look like to you. An E-mail back at sfowler@mastnet.net
Public Sub TimeOut(pInterval As Single)
Dim sngTimer As Single
sngTimer = Timer
Do While Timer &lt; sngTimer + pInterval
DoEvents
Loop
End Sub

Private Sub Command1_Click()
Static intTry As Integer
dim DB as Database
Dim A as String
Dim Dbs as Recordset
On Error GoTo errorh
set DB = Opendatabase("database Path")
set Dbs = DB.OpenRecordset("Table Name")
Dbs.Lockedits = False
Dbs.Edit
Dbs(A) = Text1
Dbs.Update
Dbs.Close
DB.Cose
Continue:
'Here trap your errors
errorh:
Select Case Err.Number
Case 3262, 3186, 3187, 3188, 3189
TimeOut 5
MsgBox "Time"
If intTry = 1 Then 'If you have 2 tries
If MsgBox("Do you want to try again?", vbRetryCancel) = vbRetry Then
intTry = 0
GoTo Continue
Else
intTry = 0
Exit Sub
End If
End If
intTry = intTry + 1
Resume
GoTo Continue
End Select
End Sub

Locking A Database
To whom may concern:

I am currently developing a database application and I am now facing quiet a number of problems. But the major one, I would like to consult you all who is expert in Visual Basic

1) The application have a toolbars on the MDI form which consists of New, Edit, Delete, Save. Whenever I click on the edit button, the text boxes from the child form will be enable, which means by default it is disable. How can I detect whether the record have been edited or not. This is because if the user just click the edit button but didn't do any editing and the user again click on the second record, a message will be prompted asking the user to save the data or not. The main point I like to detect whether the record have been edited or not because if the data have not been edited then no record saving process will be done! It will not slow down the performance of the system. I hope that you all get what I mean.

2) The application that I am currently developing is a Client-Server based application using SQL command. This is because with SQL command, we can run it on whatever databases. But the things is how can I lock a database record, any good suggestion or any sites that I can refer.


Thank you


Ken

: Export Data From Access Table To SQL SERVER Table Programatically
Dear All,

I have a table in Access. I want to export the data from that table's fields into one field of SQL SERVER database table. for eaxmple.

Let I have a table named A in access. Table A has 5 fields. Also i have a table in SQL SQRVER.Let that table name is B. I want to export data from Access table A fileds into one field of SqlServer table B.

Can you help me plz.

Database Locking Issue
I manually copy one Access 2000 database from one server to another.
After attempting to copy Access 2000 database from one server to another I sometimes get this message:


Code:
Cannot copy myDatabaseName: There has been a sharing violation.
The source or destination file may be in use.
Even if I dont get this message I still have database locking issues once in a while.

The destination database does show it is locked with a locked file.
The destination database has been set up with read only setting and has a form that is used by 3 or 4 users that is read only and doesnt allow any write capability. The form allows users to scroll and search through 200 records.
Why would it lock on the a read only database and how can I eliminate this problem?

Locking Database When VB Program Is On
How can I lock a DAO 3.6 database for only one program? For example: when I open my VB program, it will lock itself, and when I'll try changing something on another program, say Access, it won't. Changes will only be made off the VB program.

One For The Experts...Database Locking Up
Sorry about the first thread, I got knocked off line just as I was attempting to send it and it didn't have any notes. Here it is...

This really has me stumped. I am running the code included below using VB6 and attempting to pull up a report in Access 2002. The code runs without error but does not show the report at all. It simply comes back to the screen the code was called from. But.. what really surprises me, is that it locks up the database completely. Going to explorer and attempting to open the database manually fails, once again without error, but nothing happens. The database simply will not open. If I soft boot my computer, then the database is back functional and can be onpenned in explorer manually.
What am I doing wrong.

Private Sub cmdOK_Click()
Dim objAccess As Object
Dim gMSAccess As ADODB.Connection
Dim sSelect as String
Dim strDB as String

Set gMSAccess = New ADODB.Connection
sSelect = "DSN=MS Access Database;DBQ=C:YYYYTest.mdb;DefaultDir=C:YYYY;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD=XXXX;UID=;"
gMSAccess.Open sSelect

strDB = app.path & "Test.mdb"

Set appAccess = _
CreateObject("Access.Application.10")
' Open database in Microsoft Access window.
With appAccess
appAccess.OpenCurrentDatabase strDB, False, "XXXX"

Visible = True
.DoCmd.OpenReport reportname:=gsReportName, _
View:=Access.acPreview, filtername:=gsReportQueryName, wherecondition:=sBuildClause
appAccess.DoCmd.Maximize

Set appAccess = Nothing

End With
Set gMSAccess = Nothing

Locking Database File *.mdb
hello friends,

I am developing a database project of 'fees collection'.So I want to lock my database file(*.mdb) so it cant be accessed by the user without password through my project as well as through the Microsoft Access.So help me for How to lock the database file and access through the VB code.

Locking Fields In A Database
Is it possible to lock fields in tables in MS access so that they cannot be edited through VB code eg. tb_tablename.Edit
Thanks

ADO Database Locking And Timeout
I have a bit of a problem.

I am doing an update query which should complete in about 1 second with a single user.

If I deliberately lock the database by running the update on the same records from another pc, I want a fairly prompt message to the first user to that effect, so they can resave.

If I do this:-
Dim cmd As New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = csql
cmd.CommandTimeout = 5
Set cmd.ActiveConnection = cnn
Set rs = cmd.Execute(L)

The first programme hangs completely for a very long time. Minutes in fact.

If I do this instead:-

cnn.Execute csql, L
I cannot set a timeout.
If I do this:-
cmd.Execute L

The number of records updated is always 0, although the records are updated properly.

Am I missing something obvious here?

Using VB6, Access database, updating about 5 records out of 200,000.

I just need to:-
a) get the timeout working.
b) be sure that the records have actually saved when the software says they have so I can clear the save button.

In the current VB3 programme, the equivalent update will hang for 30 secs and then just return with no error, so I cannot tell whether the data has been saved or not.  Hence the move to VB6 and ADO.  It just seems to make it worse though.

Any help appreciated.  Changing the database to SQL Server etc is not an option by the way.

Peter Meachem
peter@accuflight.com

Locking One Record In An Database
Hello Everyone,

I use VB6 and my database is in ms access 2000.

In my program I want to lock a record that one user is editing. I tried to do this with recordset.LockEdits this works but my problem is that this property locks the entiry table. In this case it is not possible for other users to edit or look at other records from the same table.

Is there a option to lock the record that one person is editing and that at the same time other user from this program still can look or change other records.

Thanks in advance...
Els

Database Locking Problem
I have created a IT help desk application for multiple users which is currently in it's alpha release. The program is now working correctly in most cases.

However I have noticed problems when the user's load the software. Sometimes the program loads into memory (can be seen in the task list) but doesn't display. This is often because the database which it accesses during it's loading routine is locked. It seems OK if the program is being used elsewhere but it does tend to create this error if the actual access database is open for maintenance by myself.

The problem being that some of the database areas have not been added into the software yet and reuire manual editing. However this creates a lock on the database which is blocking the users from using the software. Is there a way to avoid this either in the software code or access itself. Also I have not 100% ruled out multiple sessions of the software not causing the same issue so if there is anything I can do to avoid it then advice is welcomed.

Thanks,

Steve Gordon

Adodc Database Locking
This problem has been bugging me for quite some time. I'm using adodc recordsets to obtain data from a microsoft access database. After a couple of runs of debugging the code, i'll get this error:

Microsoft JET Database Engine
The database has been placed in a state of by user 'Admin' on machine 'ABIDDY' that prevents it from being opened or locked.
Error Number: -2147467259

This always occurs during the conn.open line in the code:

Dim conn as new adodb.connection
Dim rcdset as new adodb.recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Main.db_Url & ";Persist Security Info=False"
conn.Open
rcdset.Open strSQL, conn, adOpenStatic, adLockReadOnly

Everytime this happens i have to close down access and reload the program.

If anyone has any idea what's causing this.. or how i can fix it.. lemme know please!
alex

Database Transaction && Locking
Hai,...

I'm using VB 6 and SQL Server 2000, i want to ask how to implement a TRANSACTION and LOCKING for SQL Server on VB 6 ?
Is it the same like sending a query string?
What is the VB 6 syntax for Transaction and Locking?


Thank you,
Rabbi

Ms Access Database Locking Via Vb6
EVEN THOUGH USERS ACCESS DIFFERENT ACCESS TABLES VIA VB6, WE USUALLY GET DATABASE LOCKING. HOW DO WE GO ABOUT IT?

THANKS

Update An SQL Server Table With Values From An MS ACCESS Table
Hi everyone in the forum. I would like to create a VB script which updates a table in an SQL Server with values from a table from my local MS ACCESS. It would be very helpful if anyone could answer how to do that.
Pleeeeeeeeeeeease !!!

PS. I 'm sorry if a thread like that already exists and i didn't see it.

Copyright 2005-08 www.BigResource.com, All rights reserved