Insert records of an array

[Erick Gamer]

Hello there I new in the forum best regards to all,


I have a problem with an array at the moment of insert records in my table, display me of error "3078, The database engine can not find the table or query of input false"


But my table if exist in my database, what can be the mistake? this is the code:

strPathcsv= .SelectedItems(1)
        
        rst.CursorLocation = adUseClient
    
    strNewPath = Left(strPathcsv, InStrRev(strPathcsv, "\") - 1) & "]." & Mid(strPathcsv, InStrRev(strPathcsv, "\") + 1)
    
    rst.Open "SELECT Top 10 * FROM [Text;Database=" & strNewPath & ";", CurrentProject.Connection, adOpenStatic, adLockReadOnly
                                      
    'Array
    ReDim flds(rst.Fields.Count - 1 - 14)
    For fld = 0 To UBound(flds)
        flds(fld) = fld + 14
    Next
    rst.Move 8 'Set focus un records 9
    tbl = rst.GetRows(2, , flds)
    rst.Close
    For fld = 0 To UBound(tbl, 1)
        CurrentDb.Execute _
        "Insert Into MyTable (C1, C2, C3, C4, C5, C6, C7, C8, C9, C10) " & _
        "Select '" & Left(tbl(fld, 0), 8) & "', '" & Right(tbl(fld, 1), 2) & "', F1, F2, F3, F9, F11, F7, F8, F10 From [Text;Database=" & strPathcsv = Where & F & fld + 1 + 14 & "='X'"
    Next

I thank you for the help you can give me....!


Erick Gamer

[ranman256]

Access doesnt really use Arrays.(old tyme methods)
it uses recordsets, strings,collections.

[Bulzie]

I would suggest put a breakpoint (click on left side grey bar so line goes red) in that code at the top, run it then step through each line (F8) to see which line is giving the error.

[Erick Gamer]

Error is in this line:

CurrentDb.Execute _
        "Insert Into & MyTable & "(C1, C2, C3, C4, C5, C6, C7, C8, C9, C10) " & _
        "Select '" & Left(tbl(fld, 0), 8) & "', '" & Right(tbl(fld, 1), 2) & "', F1, F2, F3, F9, F11, F7, F8, F10 From [Text;Database=strPathcsv Where F" & fld + 1 + 14 & "='X'"

Thanks

Erick Gamer

[aytee111]

Replace the "CurrentDb.Execute" with "Debug.Print". This will print the string in the immediate window below. Copy and paste it into a new query design and troubleshoot it from there.

[Erick Gamer]

Debug.Print show:

Insert Into & MyTable & (C1, C2, C3, C4, C5, C6, C7, C8, C9, C10) Select '33207488', '1', F1, F2, F3, F9, F11, F7, F8, F10 From [Text;Database=strPathcsv Where F15='X'

I could not find the error

[aytee111]

Did the query not complain? Maybe you should create a query and see the difference. It is plain.

[orange]

The problem seems to be here
Insert Into & MyTable &

Is MyTable the actual name of your table?

If so then you wouldn't need & MyTable

[Erick Gamer]

If it is correct MyTable, is my true table, the data that I try to enter comes from an external file taken with File Dialog

[Erick Gamer]

I linked table to my database for create the query SQL:
Example: INSERT INTO MyTable ( C1, C2, C3, C4, C5, C6, C7, C8, C9, C10) SELECT Book1.Field1, Book1.Field2, Book1.Field3, Book1.Field4, Book1.Field5, Book1.Field6, Book1.Field7, Book1.Field8, Book1.Field9, Book1.Field10

This Query SQL is get with Debug.Print

FROM Book1;
Insert Into MyTable &(C1, C2, C3, C4, C5, C6, C7, C8, C9, C10) Select '33207513', '1', F1, F2, F3, F9, F11, F7, F8, F10 From [Text;Database=strRutacsv Where F40='X'

[aytee111]

The first SQL string is the correct one. Now, open that query in design view and replace all the "Book1.fieldname" with what you have below. Such as, instead of Book1.Field1, type in '33207513' and see what the SQL looks like. Do that for each of the fields corresponding to C1, C2, etc.

[Bulzie]

Don't think you need that first "&" before the (C1.. I could be wrong.

[BogyOne]

I'm using Chr() function to avoid any issues with strings containing " or '. If I remember well MSA is changing " in the VBA code to ' in the SQL.

[June7]

The errors in your original code is the concatenation.

If your table is really named MyTable and that is not a VBA variable:

"Insert Into & MyTable & "(

should be

"Insert Into MyTable (

If MyTable is a variable:

"Insert Into " & MyTable & "(

Also have an unpaired [ in the FROM clause. I've never used that syntax so not sure where its mate goes or if [] are even needed. But need to do some more concatenation. Are F and fld VBA variables? Why are you using + 1 + 14? Might need apostrophe delimiters for the path string. And why is there an = sign in front of Where?

From [Text;Database='" & strPathcsv & "'] Where " & F & fld + 1 + 14 & "='X'"