A Command can be based on a SQL string, it does not have to be a stored procedure. There is very little speed difference between a Command executing a SQL statement vs a Stored procedure. The difference comes at the first execution only. Every sql statement needs to be "Compiled". A Stored Procedure is "Compiled" when it is saved but a SQL Statement is compiled at runtime. However, set the Command.Prepared property to True and SQL Server will only need to compile the SQL statement once. The compilation is saved in a cache and is used by every execution afterwards.
Using a Command would be more efficient than executing the sql statement directly through the Connection.Execute method. It also eliminates some of the hassles you may encounter when dynamically creating sql statements through code.
I would recommend that you open the connection before the loop begins and close it after the entire job completes.
Set objCmd = New ADODB.Command
Set .ActiveConnection = Some ADO Connection
.CommandType = adCmdText
.CommandText = "Insert Into SomeTable Values(?,?,?,?)" 'question mark for each field
.Prepared = True 'important when executing sql statements in a loop
'the database can populate the parameters collection for you
'by calling the Parameters.Refresh method.
'Note: this results in an extra database hit and more network
'traffic but shouldn't be a huge deal
'Also, using Refresh depends on the SQL Statement - make
'sure you verify that it generates the intended parameters.
'otherwise populate the Parameters Collection manually
.Parameters.Append .CreateParameter(.... 'do for each ?
Do Until EOF
'...Get File Data
'Set Parameter Values
objCmd.Parameters("ParamName").Value = File Data 'do for each parameter
objCmd.Execute , , adExecuteNoRecords
If there are lots of rows to insert, it would be more efficient to use variables declared as ADODB.Parameters, one for each field, rather than going through the Parameters Collection everytime.
One more note - If you are inserting records and need to use the new Identity column value elsewhere in your program - then you should(actually you must) use stored procedures.