using arrays in access

[chrism84]

I'm quite new to vba, and was looking for some assistance in retrieving values from an array. I was planning on making an array to store table names, then run an update query which will loop for all the tables in the array. My current code to test the array is:

Sub ImportSchemaTable()
Dim db As DAO.Database
Dim Arr(10) As String
Arr(0) = "PS0006_DELTA2"
Set db = CurrentDb()
db.Execute _
"INSERT INTO Arr(0) SELECT * FROM [Text;FMT=Fixedlength;HDR=Yes;DATABASE=C:\database\;].[PS0006#txt];", _
dbFailOnError
db.TableDefs.Refresh
End Sub

"PS0006_DELTA2" is the name of the table, but I can't get the SQL statement to retrieve this from the array. The update query runs fine if i input the table name manually, but I will not be able to loop it this way. Can anyone tell me what I am doing wrong?

Thanks

Chris

 

[Ian Mac]

You need to put the Arr(0) outside of the quotes:

"INSERT INTO " & Arr(0) & "SELECT * FROM [Text;FMT=Fixedlength;HDR=Yes;DATABASE=C:\database\ ;].[PS0006#txt];",

Hope that helps,

 

[chrism84]

That's great, thanks!

One more slight problem has arisen from this however; when i close the quotes after "INSERT INTO " i get a msgbox "Syntax error in INSERT INTO statement". Any idea why this would be?

Thanks,

Chris

 

[chrism84]

Disregard my last post; working now.

Thank you very much!