I'm writing some Excel VBA for a user to be able to insert records into a SQL Server table, using ADODB. This is working fine:
Dim conn As New ADODB.connection
conn.Open "<my connection string>"
Dim records As New ADODB.Recordset
records.CursorLocation = adUseClient
records.Open "SELECT TOP 1 * FROM [MyTable]", conn, adOpenStatic, adLockBatchOptimistic, adCmdText
' (Add some stuff here.)
records.UpdateBatch
Something's nagging me, though: is it possible to get the recordset pointed at the right table without doing a SELECT up front? This could get to be a pretty big table, so SELECT * FROM [MyTable] is out. I'm limiting that with a TOP 1, but I'm only doing a write, so it feels like I shouldn't have to do that!
The documentation makes it sound like you can just put a table name in the Source argument:
Use the optional Source argument to specify a data source using one of the following: a Command object variable, an SQL statement, a stored procedure, a table name, [...]
In practice, this doesn't work for me, and all of the following just error out with the message below, like it thinks I'm calling a stored procedure:
records.Open "MyTable", conn, ' [...]
records.Open "[MyTable]", conn, ' [...]
records.Open "[dbo].[MyTable]", conn, ' [...]
The request for procedure 'MyTable' failed because 'MyTable' is a table object.
Is there some syntax I'm missing here, or do I just have to go with the SELECT?
