CODEWITHSUNDEEP
databaseexcelms-accessvba
gaussblurinc
gaussblurinc

Reputation: 3682

Access + VBA: Correct creation of database

how to correct create db with following fields: 

Dim F As String
Dim S As Long
Dim T As Integer
Dim C As Double //Currency
Dim D As String //Date


F = "ABC"
S = 88869045
T = 9   
C = 30.4493 // currency, but can be double
D = "06.08.2010" // date (how to correct convert to date?) //#1

DBTable.Name = "TestTable"
DBTable.Columns.Append "First", adVarWChar, 40
DBTable.Columns.Append 1, adBigInt, 20
DBTable.Columns.Append 0, adInteger
DBTable.Columns.Append 0, adCurrency
DBTable.Columns.Append 0, adDate

DBCatalog.Tables.Append DBTable

also, DBCatalog as ADOX.Catalog and DBTable as ADOX.Table.

see also additional question at #1. Thanks!

Upvotes: 2

Views: 316

Answers (2)

gaussblurinc
gaussblurinc

Reputation: 3682

all works fine with this:

//create database
DBTable.Name = "TestTable"
DBTable.Columns.Append "First", adVarWChar, 40
DBTable.Columns.Append "Second", adDouble, 40
DBTable.Columns.Append "Third", adInteger
DBTable.Columns.Append "Forth", adCurrency
DBTable.Columns.Append "Fifth", adDate

But question about convertion string to date still open:

Dim D as String //must be date 
D = "06.10.2010"

how to convert it to date and after that insert into db? (types adDate and Date are compatible?)

So, show a last version of code:

// after this: insert data into datatable (not in question, but part of creation db (usage is necessary))
Dim F As String
Dim S As Double
Dim T As Integer
Dim C As Double
Dim D As Date

Dim SS As String
Dim TS As String
Dim CS As String
Dim DS As String

F = "'" + "ABC ABD ACS" + "'" //use of single (') quote
S = 88869045
T = 9
C = 30.4493
D = CDate("20/12/2010") // for any data type! don't forget about it

SS = "'" + Str(S) + "'"
TS = "'" + Str(T) + "'"
CS = "'" + Str(C) + "'"
DS = "'" + Str(D) + "'"

DBConnection.Execute "INSERT INTO TestTable VALUES  (" + F + "," + SS + "," + TS + "," + CS + "," + DS + " ) "

P.S. it works fine, thanks for comments and answers.

Upvotes: 1

HansUp
HansUp

Reputation: 97101

If your Access version is 2000 or later, you can use the Replace() function to substitute dashes for the dots in your date string. Then the CDate() function will be able to convert the string to a Date/Time value.

Debug.Print CDate(Replace("06.08.2010",".","-"))
6/8/2010

That was an example from the Immediate window on my system, which uses US as its locale setting. So CDate() can accept a string containing a date in mm-dd-yyyy format. If your locale is different, you will need to put in more work to get what you need.

Upvotes: 2

Related Questions