Pass Variables to Subs/Functions confusion

S

Stuart

I'm running a routine on selected workbooks in a folder.
The aim is to import these workbooks into Access.

As a result I have the following variables:

SourceDir : original full path of Xl folder (a String)
SaveDir : typically: SourceDir & "\" & "Access Files"
EstimateName : an element of the SourceDir string which
needs to become the Access Project name
DataBaseName : the name of the workbook currently being
without the xls extension
TableName : the worksheet name

Do not know if this is correct/feasible, but would hope to end
with a new folder named SaveDir, containing an Access Project
named EstimateName, which contains Databases (each named
according to the original Workbook name, and where each
Database contains Tables named according to the original
worksheets in that Workbook.

Probably wrong about my guess as to this structure in Access,
so education most welcome.

However, this is the code I found, that I would like to pass
the variables to:

Option Explicit

Function DbSource()
DbSource = ThisWorkbook.Path & "\" & ctDb & ".mdb"
End Function

Function DbConnection()
DbConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & DbSource & ";"
End Function

'' *************************************************
'' Purpose : Cover to create an Access database
'' Written : 16-Oct-2002 by Andy Wiggins - Byg Software Ltd
''
Sub CreateDb()
CreateADatabase DbSource
End Sub

'' **************************************************
'' Purpose : Create an Access database
'' Written : 16-Oct-2002 by Andy Wiggins - Byg Software Ltd
''
Sub CreateADatabase(vtDbName$)
Dim cat As New ADOX.Catalog
Dim vtMessage$

On Error GoTo ErrorHandler
cat.Create DbConnection & ";Data Source = " & DbSource

Exit Sub

ErrorHandler:

vtMessage = "Database creation error"
vtMessage = vtMessage & _
Chr(10) & _
Chr(10) & "Error Number: " & Err & _
Chr(10) & "Error Description: " & Error()

MsgBox vtMessage, vbInformation, ctByg

End Sub

This is just the first part of the code, but help in this area may well
help me resolve later problems.

Would be grateful for assistance, please.

Regards.
 
Top