To create tables with VBA - To name the new table from a mask'txt box

[Amos]

I built the following Sub, in order to create tables automatically.
The button is placed on a mask where there are 3 txt box, two of them
contain the "Bank name" and "Bank a/c". I want to name the new table with a
string composed by the "Bank name"&"Bank a/c", How I could get it?
Using the following Sub I get always the same name "NEWBANKAC".

Any Tip is welcome.

Thank you.



Private Sub cmdADDNEWBANKAC_Click()
On Error GoTo TableErrCatcher
Dim cat1 As ADOX.Catalog
Dim tbl1 As ADOX.Table
Dim str1

'Reference objects for table
Set cat1 = New Catalog
cat1.ActiveConnection = CurrentProject.Connection
Set tbl1 = New Table


'Name table and append columns
With tbl1
.Name = "NEWBANKAC"
.Columns.Append "ID N", adInteger
.Columns.Append "DATE", adDate, 10
.Columns.Append "FIN CODE", adLongVarWChar, 4
.Columns.Append "DESCRIPTION", adVarWChar, 30
.Columns.Append "DEBIT", adDouble
.Columns.Append "CREDIT", adDouble

End With

'Append new table to Tables collection
'and free catalog resource
cat1.Tables.Append tbl1
Set cat1 = Nothing

str1 = NewBankac & "new bank account has been created"
MsgBox str1, vbInformation, "Cash Flow Management"

TableErrCatcher:
If Err.Number = -2147217857 Then
MsgBox "Bank account already existing", vbInformation, "Cash Flow
Management"
End If
Debug.Print Err.Number, Err.Description

End Sub

 

[Andrew Smith]

Amos,

You don't give enough information to say how you could get the bank name and
account no. Where do these come from - some information that is already in
the database, or from user input? If the latter then you could use the
InputBox function to get the values before the table is created.

However, I wonder why you should need to do this at all. Can't you just
create a BankAccounts table with fields for bank name and account number,
and link this table to a transactions table containing the Date, Code,
Description, Debit and Credit fields?

 

[Tim Ferguson]

I want to name the new table with a
string composed by the "Bank name"&"Bank a/c",

Bad, bad, bad, bad, bad, bad, bad, bad idea. Put data in fields, not in
table names. Check out some primers in R theory, normal form, etc.

Tim F

 

[TC]

Um, you have some reading to do!

Start here: http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC