Solved

Vb6 from Access to Sql 2008 R2 ADO OLEDB

Posted on 2011-03-08
13
1,591 Views
Last Modified: 2013-12-25
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
Comment
Question by:pupulin
  • 5
  • 4
  • 2
13 Comments
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 35069894
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35069922
Why not change the data type back to DATETIME?
0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 35070058
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 1

Author Comment

by:pupulin
ID: 35070115
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35070139
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
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 35070422
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
 
LVL 1

Author Comment

by:pupulin
ID: 35070423
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
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 35070529
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
 
LVL 1

Accepted Solution

by:
pupulin earned 500 total points
ID: 35070585
i change Provider Connection and ok.

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

Thanks
0
 
LVL 14

Expert Comment

by:Brook Braswell
ID: 35070621
so problem is resolved?
0
 
LVL 1

Author Comment

by:pupulin
ID: 35121316
i change de provider
from Provider=SQLOLEDB; Server=HSPRP\SQLEXPRESS;"
To
"Provider=SQLNCLI10; Server=HSPRP\SQLEXPRESS;"
and  OK.

option 4

0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question