Accessing All Sheets in a closed workbook
Here's some comments.
PrepareDatabase creates a Connection to your database but because the
variable is local to this sub procedure you are allowing it to go out
of scope i.e. the connection is immediately closed when the sub
procedure ends. [Aside: change the declarations of all your object
variables from the single line declare-and-instantiate:
Dim conn As New ADODB.Connection
to the two line:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
..]
To keep the connection open, you have some choices:
- change from Sub to Function of return type ADODB.Connection and set
the return to be your conn variable (recommended);
- pass an empty Connection variable as a ByRef argument and use this
object to create the connection;
- change the scope of your conn variable to make it visible to all
procedures (not recommended).
CreateDatabaseRecord doesn't use a Connection object - you need one to
open your recordset, something else you've is omitted (perhaps you
have snipped some code?) Here a suggestion:
Sub CreateDatabaseRecord( _
ByVal ActiveConn As ADODB.Connection, _
ByVal TableName As String _
)
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = ActiveConn
.CommandType = adCmdTable
.CommandText = TableName
End With
Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseServer
.CursorType = adOpenStatic
.LockType = adLockOptimistic
Set .Source = cmd
.Open
' Do things with recordset here
End With
Nothing else jumps out at me. If you are having specific errors, post
back with some details.
--
|