Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1644
  • Last Modified:

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

0
pupulin
Asked:
pupulin
  • 5
  • 4
  • 2
1 Solution
 
Brook BraswellApplication Development ManagerCommented:
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...
0
 
BrandonGalderisiCommented:
Why not change the data type back to DATETIME?
0
 
Brook BraswellApplication Development ManagerCommented:
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().
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
pupulinAuthor Commented:
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
0
 
BrandonGalderisiCommented:
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.
0
 
Brook BraswellApplication Development ManagerCommented:
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 ? )
 
0
 
pupulinAuthor Commented:
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
0
 
Brook BraswellApplication Development ManagerCommented:
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;"
0
 
pupulinAuthor Commented:
i change Provider Connection and ok.

Provider=SQLOLEDB; Server=HSPRP\SQLEXPRESS;"
to
"Provider=SQLNCLI10; Server=HSPRP\SQLEXPRESS;"
and OK.

Thanks
0
 
Brook BraswellApplication Development ManagerCommented:
so problem is resolved?
0
 
pupulinAuthor Commented:
i change de provider
from Provider=SQLOLEDB; Server=HSPRP\SQLEXPRESS;"
To
"Provider=SQLNCLI10; Server=HSPRP\SQLEXPRESS;"
and  OK.

option 4

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now