Avatar of pupulin
pupulin
 asked on

Vb6 from Access to Sql 2008 R2 ADO OLEDB

I have a system make in VB6 with Access.
and now i need use sql 2008 r2 too.
my program will have option Access or Sql.
when i use access the command
RS.Fields("Data") = Now()
is ok.

When i use Sql server give a error
Run-time error '-2147217887 (80040e21)'
because when i migrate using
Microsoft Sql Server Migration Assistent 2008
the field DateTime convert to DateTime2(0) in Sql server.

How i use de same command
RS.Fields("Data") = Now()
to access and sql server?
whithout change de type of date because i many 300 tables
in data base and i have 10 data base to migrate.

If somebody help me i apreciate.

Romualdo

Visual Basic ClassicMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
pupulin

8/22/2022 - Mon
Brook Braswell

Romualdo,
  I would have to ask are you using ADO?  and how are you opening the recordset?
  I also have this database and the NOW function works fine...
  Perhaps you have the table open as readonly...
BrandonGalderisi

Why not change the data type back to DATETIME?
Brook Braswell

Romualdo,
   The only difference in a datetime and a datetime2 is that the datetime2 holds the fractions of a second and the range is from 1AD to 12/31/9999.
The Now() function should work fine but the warning here is that Now() gives the time of the computer running the application.  If you are really wanting a system time you should use a server datetime function such as getdate().
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
pupulin

ASKER
Brook1966:
I open sql server
 "Provider=SQLOLEDB; Server=HSPRP\SQLEXPRESS; Database=Name;"
...
    RS.Open "select * from Tb_log where 1 = 0", Conectx, adOpenKeyset, adLockOptimistic
    RS.AddNew
    RS.Fields("GrupoEmpresarial") = yGrupoEmpresarial
    RS.Fields("Sequencia") = ySequenciaMnu_Menu
    RS.Fields("ChUnUsuario") = G_ChUnUsuario
    RS.Fields("Data") = Now()
    RS.Fields("Empresa") = GEmp_Grupo
    RS.Fields("Obs") = strObs
    RS.Update
    RS.Close

BrandonGalderisi:
if i don't find a better way i will change data type back.
i don't like to do this because there are many tables (300)
in data base and i have 10 data base to migrate.

---
thanks
BrandonGalderisi

I didn't say it was easy.  The migration wizard is meant to guide you and you don't necessarily need or want to accept all the defaults they provide.
Brook Braswell

Few more questions...
   This works on my end with same type...
   1.  Is your cursor location Server or Client ( i would presume server )
   2.  What is the regional time setting for your computer ?
   3. Do you have an ODBC for this connection ?  ( is that even an option for you ? )
 
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
pupulin

ASKER
I will study about the Microsot Sql Server Migration Assistant.
how to change  move DateTime access to DateTime Sql Server.
or i will write aplication with Adox that change DateTime2 back to DateTime.

thanks
Brook Braswell

another way...

IN YOUR VB6
run this query
SELECT table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.COLUMNS
where data_type = "datetime2"

then run update statements on each row of your recordset
SQL = "ALTER TABLE " & RS!TABLE_NAME & " ALTER COLUMN " & RS!COLUMNS_NAME & " datetime;"
ASKER CERTIFIED SOLUTION
pupulin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Brook Braswell

so problem is resolved?
Your help has saved me hundreds of hours of internet surfing.
fblack61
pupulin

ASKER
i change de provider
from Provider=SQLOLEDB; Server=HSPRP\SQLEXPRESS;"
To
"Provider=SQLNCLI10; Server=HSPRP\SQLEXPRESS;"
and  OK.

option 4