Urgent : HELP Me Plea......se

  • Thread starter Ashish Kanoongo
  • Start date
A

Ashish Kanoongo

Please review the code, andletmeknow whats wrong I am doing

*-----------------------------------------------------
Dim rsInvoiceRecord As New ADODB.Recordset
Dim oConn As New ADODB.Connection

Sql = "drop table tmpExcel"
If oConn.State = 1 Then oConn.Close
oConn.Open CurrentProject.Connection
oConn.Execute Sql
oConn.Close
*-------------------------------------------1st Error
The Database has been placed in a state by user 'Admin' on machine 'devp' that prevents it from being opened or locked
*------------------------------------------------ Once I skip/ignore this

Sql = "CREATE TABLE tmpExcel ([Account Name] text(30), [Invoice No] integer,[Billing Assets Quarter] currency default 0,[Advance Billing] currency default 0,[Capse Dist] currency default 0,"
For j = LBound(arrcode) To UBound(arrcode)
If arrcode(j) <> "" Then
Sql = Sql + arrcode(j) + " currency default 0, "
End If
Next

*-------------------------------------------2nd Error (Give me same error again)
The Database has been placed in a state by user 'Admin' on machine 'devp' that prevents it from being opened or locked
*------------------------------------------------

rsInvoiceRecord.ActiveConnection = CurrentProject.Connection
rsInvoiceRecord.CursorLocation = adUseClient
rsInvoiceRecord.Open "Select * from tmpExcel", , adOpenDynamic, adLockOptimistic

*-------------------------------------------3rdError (Give me same error again)
The Microsoft Jet database engine could not find the object.....
If here I stay 1-2 min in debug mode, then I run, it execute perfectly. It seems here it is looking for some delay. I also tried DoEvents, but not able to make it workable.
*------------------------------------------------
rsInvoiceRecord.AddNew
rsInvoiceRecord.Fields(0).Value = rsDetailInvoice.Fields(0).Value
..
..
..
rsInvoiceRecord.Update
rsInvoiceRecord.Close

*-----------------------------------------------------

I tried various option, please let me how do I handle this?

Ashish K
 
G

George Nicholson

AFAIK you can't set the Default property of a field via SQL, that is why
DEFAULT 0 doesn't work. When creating a table, SQL only allows you to name
a new field, set it's Type/size and create indexes.

Once the table is created you can modify the various properties of the new
field/table via DAO or ADO in a separate step, but you can't do it all at
once.

Hope this helps,


--
George Nicholson

Remove 'Junk' from return address.


I tried docmd.runsql for delete table sqland create table sql. Delete
tablesql works great , but incase of create teble sql it give me error in
following sql

CREATE TABLE tmpExcel ([Account Name] text(30), [Invoice No] integer,
[Billing Assets Quarter] CURRENCY DEFAULT 0 , [Advance Billing] currency ,
[Capse Dist] currency )

Then I directly tried in access query, it give me error on "DEFAULT 0", if i
remove this,it works perfectly.

Whats wrong I am doing? Am I useing correct syntax?
Please review the code, andletmeknow whats wrong I am doing

*-----------------------------------------------------
Dim rsInvoiceRecord As New ADODB.Recordset
Dim oConn As New ADODB.Connection

Sql = "drop table tmpExcel"
If oConn.State = 1 Then oConn.Close
oConn.Open CurrentProject.Connection
oConn.Execute Sql
oConn.Close
*-------------------------------------------1st Error
The Database has been placed in a state by user 'Admin' on machine 'devp'
that prevents it from being opened or locked
*------------------------------------------------ Once I skip/ignore this

Sql = "CREATE TABLE tmpExcel ([Account Name] text(30), [Invoice No]
integer,[Billing Assets Quarter] currency default 0,[Advance Billing]
currency default 0,[Capse Dist] currency default 0,"
For j = LBound(arrcode) To UBound(arrcode)
If arrcode(j) <> "" Then
Sql = Sql + arrcode(j) + " currency default 0, "
End If
Next

*-------------------------------------------2nd Error (Give me same error
again)
The Database has been placed in a state by user 'Admin' on machine 'devp'
that prevents it from being opened or locked
*------------------------------------------------

rsInvoiceRecord.ActiveConnection = CurrentProject.Connection
rsInvoiceRecord.CursorLocation = adUseClient
rsInvoiceRecord.Open "Select * from tmpExcel", , adOpenDynamic,
adLockOptimistic

*-------------------------------------------3rdError (Give me same error
again)
The Microsoft Jet database engine could not find the object.....
If here I stay 1-2 min in debug mode, then I run, it execute perfectly. It
seems here it is looking for some delay. I also tried DoEvents, but not able
to make it workable.
*------------------------------------------------
rsInvoiceRecord.AddNew
rsInvoiceRecord.Fields(0).Value = rsDetailInvoice.Fields(0).Value
 
A

Ashish Kanoongo

Finally I did it, Please review the following code and let me know any other recommendations

*---------------------------------------------------------------------------------------------------*
SQL = "drop table tmpExcel"
DoCmd.RunSQL SQL
Set DB = CurrentDb
Dim TD As TableDef
Set TD = DB.CreateTableDef("tmpExcel") 'create a table
With TD
.Fields.Append TD.CreateField("Account Name", dbText)
.Fields(0).Size = 30
.Fields.Append TD.CreateField("Invoice No", dbInteger)
.Fields.Append TD.CreateField("Billing Assets Quarter", dbCurrency)
.Fields(2).DefaultValue = 0
.Fields.Append TD.CreateField("Advance Billing", dbCurrency)
.Fields(3).DefaultValue = 0
.Fields.Append TD.CreateField("Capse Dist", dbCurrency)
.Fields(4).DefaultValue = 0
.Fields.Append TD.CreateField("Available to Dist", dbCurrency)
.Fields(5).DefaultValue = 0
fldCount = 0
For j = LBound(arrcode) To UBound(arrcode)
If arrcode(j) <> "" Then
.Fields.Append TD.CreateField(arrcode(j), dbCurrency)
.Fields(j).DefaultValue = 0
fldCount = fldCount + 1
End If
Next
.Fields.Append TD.CreateField("Total To Dist", dbCurrency)
.Fields(fldCount).DefaultValue = 0
fldCount = fldCount + 1
.Fields.Append TD.CreateField("AIPFlag", dbCurrency)
.Fields(fldCount).DefaultValue = 0
DB.TableDefs.Append TD
End With
DB.TableDefs.Refresh
*---------------------------------------------------------------------------------------------------*
George Nicholson said:
AFAIK you can't set the Default property of a field via SQL, that is why
DEFAULT 0 doesn't work. When creating a table, SQL only allows you to name
a new field, set it's Type/size and create indexes.

Once the table is created you can modify the various properties of the new
field/table via DAO or ADO in a separate step, but you can't do it all at
once.

Hope this helps,


--
George Nicholson

Remove 'Junk' from return address.


I tried docmd.runsql for delete table sqland create table sql. Delete
tablesql works great , but incase of create teble sql it give me error in
following sql

CREATE TABLE tmpExcel ([Account Name] text(30), [Invoice No] integer,
[Billing Assets Quarter] CURRENCY DEFAULT 0 , [Advance Billing] currency ,
[Capse Dist] currency )

Then I directly tried in access query, it give me error on "DEFAULT 0", if i
remove this,it works perfectly.

Whats wrong I am doing? Am I useing correct syntax?
Please review the code, andletmeknow whats wrong I am doing

*-----------------------------------------------------
Dim rsInvoiceRecord As New ADODB.Recordset
Dim oConn As New ADODB.Connection

Sql = "drop table tmpExcel"
If oConn.State = 1 Then oConn.Close
oConn.Open CurrentProject.Connection
oConn.Execute Sql
oConn.Close
*-------------------------------------------1st Error
The Database has been placed in a state by user 'Admin' on machine 'devp'
that prevents it from being opened or locked
*------------------------------------------------ Once I skip/ignore this

Sql = "CREATE TABLE tmpExcel ([Account Name] text(30), [Invoice No]
integer,[Billing Assets Quarter] currency default 0,[Advance Billing]
currency default 0,[Capse Dist] currency default 0,"
For j = LBound(arrcode) To UBound(arrcode)
If arrcode(j) <> "" Then
Sql = Sql + arrcode(j) + " currency default 0, "
End If
Next

*-------------------------------------------2nd Error (Give me same error
again)
The Database has been placed in a state by user 'Admin' on machine 'devp'
that prevents it from being opened or locked
*------------------------------------------------

rsInvoiceRecord.ActiveConnection = CurrentProject.Connection
rsInvoiceRecord.CursorLocation = adUseClient
rsInvoiceRecord.Open "Select * from tmpExcel", , adOpenDynamic,
adLockOptimistic

*-------------------------------------------3rdError (Give me same error
again)
The Microsoft Jet database engine could not find the object.....
If here I stay 1-2 min in debug mode, then I run, it execute perfectly. It
seems here it is looking for some delay. I also tried DoEvents, but not able
to make it workable.
*------------------------------------------------
rsInvoiceRecord.AddNew
rsInvoiceRecord.Fields(0).Value = rsDetailInvoice.Fields(0).Value
.
.
.
rsInvoiceRecord.Update
rsInvoiceRecord.Close

*-----------------------------------------------------

I tried various option, please let me how do I handle this?

Ashish K
Click to expand...
 
K

Kent McLain

I had a simular problem and after searching MS Knowledge
base found out that you need to use additional statments
in the rs.open statement. I used the following because
you need to specify the type of locking being used. Per
MS article

'Open the recordset
rsCandidate.Open "Select * FROM Candidates",
CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

In short you can use the intellisense to help fill in the
blanks ie after you finish the part in quotes and specify
the connection you need to specify at a min
adlockoptimistic other lock type.

Hope this helps let me know. It got rid of my recordset
errors
-----Original Message-----
I tried docmd.runsql for delete table sqland create
Click to expand...
table sql. Delete tablesql works great , but incase of
create teble sql it give me error in following sql
CREATE TABLE tmpExcel ([Account Name] text(30), [Invoice
Click to expand...
No] integer, [Billing Assets Quarter] CURRENCY DEFAULT
0 , [Advance Billing] currency , [Capse Dist] currency )
Then I directly tried in access query, it give me error
Click to expand...
on "DEFAULT 0", if i remove this,it works perfectly.
Whats wrong I am doing? Am I useing correct syntax?
"Ashish Kanoongo" <[email protected]> wrote
Click to expand...
in message news:%[email protected]...
Please review the code, andletmeknow whats wrong I am doing

*-----------------------------------------------------
Dim rsInvoiceRecord As New ADODB.Recordset
Dim oConn As New ADODB.Connection

Sql = "drop table tmpExcel"
If oConn.State = 1 Then oConn.Close
oConn.Open CurrentProject.Connection
oConn.Execute Sql
oConn.Close
*-------------------------------------------1st Error
The Database has been placed in a state by
Click to expand...
user 'Admin' on machine 'devp' that prevents it from
being opened or locked
*------------------------------------------------ Once I skip/ignore this

Sql = "CREATE TABLE tmpExcel ([Account Name] text(30),
Click to expand...
[Invoice No] integer,[Billing Assets Quarter] currency
default 0,[Advance Billing] currency default 0,[Capse
Dist] currency default 0,"
For j = LBound(arrcode) To UBound(arrcode)
If arrcode(j) <> "" Then
Sql = Sql + arrcode(j) + " currency default 0, "
End If
Next

*-------------------------------------------2nd Error (Give me same error again)
The Database has been placed in a state by
Click to expand...
user 'Admin' on machine 'devp' that prevents it from
being opened or locked
*------------------------------------------------

rsInvoiceRecord.ActiveConnection = CurrentProject.Connection
rsInvoiceRecord.CursorLocation = adUseClient
rsInvoiceRecord.Open "Select * from tmpExcel", ,
Click to expand...
adOpenDynamic, adLockOptimistic
*-------------------------------------------3rdError (Give me same error again)
The Microsoft Jet database engine could not find the object.....
If here I stay 1-2 min in debug mode, then I run, it
Click to expand...
execute perfectly. It seems here it is looking for some
delay. I also tried DoEvents, but not able to make it
workable.
*------------------------------------------------
rsInvoiceRecord.AddNew
rsInvoiceRecord.Fields(0).Value = rsDetailInvoice.Fields(0).Value
.
.
.
rsInvoiceRecord.Update
rsInvoiceRecord.Close

*-----------------------------------------------------

I tried various option, please let me how do I handle this?

Ashish K
Click to expand...
02/12/2003
 
Top