Problem with newly created table

[incagold]

Hi guys,

Newbie here again. I have a problem with being able to use a table I just created via code (visual basic, SQL statement and ado). When I try to open this table to use the records that were just inserted, I get an error message that the table is not found. When I look at the database (access2000) the table is there. Is there something like a refresh that must be done in vb or ado that will allow immediate use of the new table?

Here is the portion of code that fails:

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Default_LineItemLayout;", cn, _
adOpenDynamic, adLockPessimistic
strSQL = "INSERT INTO " & strTBL1 & " "
strSQL = strSQL & "( [Record code], [Schedule reference number], "
strSQL = strSQL & "[Line item number], [Line item description], "
strSQL = strSQL & "[CA/GF], [Condition code] "
strSQL = strSQL & "FROM Default_LineItemLayout, " & strTBL2 & ";"
DoCmd.RunSQL strSQL
rs.Close
Set rs = Nothing
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM " & strTBL1 & ";", cn, _
adOpenDynamic, adLockPessimistic
'*** Establish serialized line item number
Dim lngLINum As Long
lngLINum = 1
rs.MoveFirst
Do While Not rs.EOF
With rs
If lngLINum >= 100 And lngLINum < 1000 Then
![Line item number] = "0" & Trim(Str(lngLINum))
ElseIf lngLINum >= 10 And lngLINum < 100 Then
![Line item number] = "00" & Trim(Str(lngLINum))
ElseIf lngLINum > 0 And lngLINum < 10 Then
![Line item number] = "000" & Trim(Str(lngLINum))
End If
.Update
End With
lngLINum = lngLINum + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

strTBL1 and strTBL2 sre variables passed to the SQL statement

If I try and run an update (above) against the table just created here, the error that the table is not available displays.

Thanks in advance for your help. It is always sincerely appreciated.

BEF

 

[genomon]

Where is your connection object & connection string?

 

[incagold]

Hi genomon,

Thank you for the reply.

Here is the connection string.

'*** Open connection to PDOORS.mdb
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PDOORS\Database\PDOORS.mdb;" & _
"User Id=admin;" & _
"Password="


BEF

 

[wangdong]

where are the definitions for the strTBL1 and strTBL2?

 

[incagold]

Hi wangdong,

Thanks for taking the time to try and help. The values for strTBL1 and strTBL2 are taken from a list of tables names in a different segment of the program and fed into the sub routine where they are used to build a working table (defined by strTBL1).

The create table (strTBL1) works fine, the table is created and populated with the expected results. The table defined by strTBL2 also exists. This we confirm by looking at the database window where both tables clearly exist. The problem occurs in the next section where we try to update one field in the newly created table (strTBL1). The program halts and we are given the message "The microsoft jet engine cannot find the input file ...." The missing (?) file is the table created as strTBL1.

Being new and ignorant I guess I am confused as to why the table is created properly and clearly exists but the program is unable to find it. Is there a timing issue or a problem in the tables collection that does not include the new table ? (bear with me I am not sure that I am using the correct terminology)

Thank you so much for trying to help. I hope I have described the problem in a reasonable manner. This is really frustrating.

BEF