Database is locked when executing insert multi row
-
Hi all, I made a very simple program to manipulate with DB as below 1. I have a SQL Server DB. The database contains just only 2 tables 1.1 TEST1(KEYCODE, SEIHIND, TEXT1, TEXT2, TEXT3), KEYCODE is primary key, auto increase 1.2 TEST2(TEXT1, TEXT2, TEXT3) no key 2. I make a new MDB by MS Access 2007 and created link table to these above SQL DB via ODBC(SQL Server driver) 3. My program uses DAO 3.6 to insert data to SQL via MS Access 3.1 The bussiness is to select data from TEST2 and insert to TEST1 3.2 SQL is like INSERT INTO TEST1(SEIHIND, TEXT1,TEXT2,TEXT3) SELECT '1', TEXT1, TEXT2, TEXT3 FROM TEST2 4. To obtain above, i made a simple program like below: Private Sub Command1_Click() Dim defWsk As DAO.Workspace Dim dbs As DAO.Database Dim sqlStr As String Set defWsk = DBEngine.Workspaces(0) Set dbs = defWsk.OpenDatabase("F:\Tin\PQMC.MDB") defWsk.BeginTrans sqlStr = "INSERT INTO TEST1(SEIHIND, TEXT1,TEXT2,TEXT3) " & _ "SELECT '1', TEXT1, TEXT2, TEXT3 " & _ "FROM TEST2 " Debug.Print sqlStr dbs.Execute (sqlStr) defWsk.CommitTrans End Sub 5. Issue: The problem when execting above code is "data is not inserted to TEST1, TEST1 is blocked(I could not execute SELECT * FROM TEST1 in SQL Analyser)" When i close program, table is not locked(I could execute SELECT * FROM TEST1 in SQL Analyser) Notes: I take above SQL and execute in SQL Analyser. it's OK Anyone know how to solve this problem, please help me. Thank you very much for your support. Tin Le,