Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Vb6 from Access to Sql 2008 R2 ADO OLEDB

Posted on 2011-03-08
13
Medium Priority
?
1,640 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

885 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