Access + VBA: Correct creation of database

Question

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!

HansUp · Accepted Answer · 2012-05-06 20:48:08Z

2

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.

answered May 6, 2012 at 20:48

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gaussblurinc Over a year ago

thanks:) D = CDate("20/12/2010") works for me:) and MsgBox D shows 20.12.2010 :)

gaussblurinc · Accepted Answer · 2012-05-06 21:39:51Z

1

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.

edited May 6, 2012 at 21:39

answered May 6, 2012 at 20:43

gaussblurinc

3,69210 gold badges38 silver badges66 bronze badges

1 Comment

gaussblurinc Over a year ago

thanks! change it to full answer for my question with your help, @HasnUP. maybe not optimal, but work

Collectives™ on Stack Overflow

Access + VBA: Correct creation of database

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related