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...
 
Top