ansaurus

Question

Inserting a date into an access db in vb

Answer 1

A:

why don't you let access handle the date:

"INSERT INTO Sales VALUES (NOW())"

Jabezz 2009-08-20 10:40:07

I tried that but it doesnt like the format thats been entered into the db then. I think it might be trying to add the time and the date with that command.

Shane Fagan 2009-08-20 10:44:02

If you're letting "Access" handle it, and you want a data, you'd use Date() instead of Now(), as Now() returns both date and time. But from outside of Access itself, neither of those functions will work reliably. Secondly, you're depending on it getting optimized properly, i.e., that it won't execute for every row. Resolving the date value before passing it to the processing database engine guarantees it won't be mis-optimized.

David-W-Fenton 2009-08-20 19:14:01

Answer 2

A:

If you didn't what to use the SQL NOW() you could wrap the date in quotes:

SQLString = "INSERT INTO (Sales) VALUES ("

SQLString += "'" & Date.Today & "')"

danseagrave 2009-08-20 10:45:37

The quotes are for strings though? It needs to be exact for the db to take the info.

Shane Fagan 2009-08-20 10:49:31

In all version of Access I've used it will accept dates, into date columns, using single quotes. Just as SQL Server doesThe only difference is in Access VBA where you can use # (hash) insted of single quote.As Tikeb says - you'd also benefit from using cultureInfo to make use the data is in a format that Access will interpret correctly (either ISO YYYYMMDD or American MMDDYYYY).

danseagrave 2009-08-20 10:59:32

Ah ill give it a try, I just thought that dates wouldnt use quotes.

Shane Fagan 2009-08-20 11:38:44

The difference is what data access method you're using. Via DAO, you use the # delimiter. In Access directly, you'll use # if you're using the default "SQL 89" querying mode, and ' if using "SQL 92." If you're using ADO, you'll use '. In VBA, you'll use the one appropriate to the data interface layer you're using, i.e., DAO or ADO. From outside Access, again, if ADO, ', and if DAO, #.

David-W-Fenton 2009-08-20 19:12:26

Thanks David, I have vague recollections about the # thing being DAO only. But clarifying that DAO uses SQL 89 (and hence #) helps. Cheers :)

danseagrave 2009-08-21 09:52:34

Answer 3

A:

You could use CultureInfo to set the formats of your datetimes?

Tikeb 2009-08-20 10:50:35

CultureInfo doesnt do dates

Shane Fagan 2009-08-20 10:53:13

We use CultureInfo to make sure the dates are formatted en-GB : dd/mm/yyyy instead of US mm/dd/yyyy. Was just thinking maybe that could be the problem, if not aww well..

Tikeb 2009-08-20 11:03:17

ansaurus

tags:

views:

answers:

Inserting a date into an access db in vb

related questions