Solved

Access to SQL Server

Posted on 1999-01-18
29
231 Views
Last Modified: 2013-12-25
I have not done that much with VB and databases and would like to know how I would go about copying all of the data from a table in Access to a table that was created for the data in SQL Server.  I was trying to use the "Data Environment Designer" that comes with VB6 to get this done but have not been able to figure out a way to do it (hard to figure out seeing as that MS didn't provide any documentation for it).  Thank you for any help you can give.
0
Comment
Question by:fatalerror080298
  • 14
  • 10
  • 3
  • +2
29 Comments
 
LVL 3

Expert Comment

by:cognition
ID: 1499148
If you are using SQL Server 7, then investigate Data Transformation services, which is part of SQL Server. This will automate and/or schedule it all for you.

You can write a small program in VB that will accomplish what you want more quickly than trying to get the DED to work.

The following uses RDO.

Dim Connection1 As rdoConnection
Dim Connection2 As rdoConnection
Dim Environment As rdoEnvironment
Dim DSN as string
Dim UID as string
DSN = <Data Source Name>
UID = <user id>
Password = <password >
Database = <Database name>

    Set Environment = rdoEnvironments(0)
    ' Set up connection1
    Set Connection1 = Environment.OpenConnection(dsName:=DSN, Connect:="uid=" & UID & ";pwd=" & Password & ";database:=" & Database, Prompt:=rdDriverNoPrompt)






0
 
LVL 3

Expert Comment

by:cognition
ID: 1499149
Oops !


Dim Connection1 As rdoConnection
Dim Connection2 As rdoConnection
Dim Environment As rdoEnvironment
Dim DSN as string
Dim UID as string
DSN = <Data Source Name>
UID = <user id>
Password = <password >
Database = <Database name>

    Set Environment = rdoEnvironments(0)
    ' Set up connection1 to access
    Set Connection1 = Environment.OpenConnection(dsName:=DSN, Connect:="uid=admin;pwd=;database:=c:\my.mdb", Prompt:=rdDriverNoPrompt)

    ' Set up connection to sql server
    Set Connection2 = Environment.OpenConnection(dsName:=DSN, Connect:="uid=" & UID & ";pwd=" & Password & ";database:=" & Database, Prompt:=rdDriverNoPrompt)

Dim rs as rdoresultset
Dim rs2 as rdoresultset

' Get tyhe access data
Set rs1 = Connection1.OpenResultset("SELECT * FROM TableA")
' Open a dummy resultset to add records
Set rs2 = Connection2.OpenResultset("SELECT * FROM TableA WHERE 1 = 2", rdOpenKeyset, rdConcurRowver)

While Not rs1.eof
    with rs2
       .addnew
       !field1 = rs1.field1
       !field2 = rs1.field2
       ....
       !fieldn = rs!fieldn
       .update
    end with
Wend

set rs1 = nothing
set rs2 = nothing
set connection1 = nothing
set connection2 = nothing
set environment = nothing

0
 
LVL 3

Expert Comment

by:cognition
ID: 1499150
You coud probably create a slicker solution using ADO, by using a disconnected resultset.

Create the resultset from an access connection, and then attach it to a SQL Server connection, and update.
0
 

Author Comment

by:fatalerror080298
ID: 1499151
This does not show me how I would copy all of the data out of a table in the Access database into a table that was set up for it in the SQL Server database.
0
 

Author Comment

by:fatalerror080298
ID: 1499152
oopss....  Didn't get to see the other comments you put up.  Let me look at that.
0
 
LVL 3

Expert Comment

by:jjbyers
ID: 1499153
Is there any reason you couldn't link the sql table to your access DB and create an update query?
0
 

Author Comment

by:fatalerror080298
ID: 1499154
Yes, but we keep having problems with it because the table has over a million rows.
0
 

Author Comment

by:fatalerror080298
ID: 1499155
Yes, but we keep having problems with it because the table has over a million rows.
0
 

Author Comment

by:fatalerror080298
ID: 1499156
Yes, but we keep having problems with it because the table has over a million rows.
0
 

Author Comment

by:fatalerror080298
ID: 1499157
Sorry about that.  Problem with system caused my message to post more then once.
0
 

Author Comment

by:fatalerror080298
ID: 1499158
I have been playing with it and have been unable to get the above code to work.
0
 

Expert Comment

by:RUSTY
ID: 1499159
The upsizing wizard in Access can port db's to SQL Server, I understand.
0
 
LVL 1

Expert Comment

by:sfranklyn
ID: 1499160
For over a million rows table in Access this is a hard problem.
I recommend exporting the table into text file then use BCP utility from SQL Server to upload the table. Good luck.
0
 
LVL 3

Expert Comment

by:jjbyers
ID: 1499161
With over a million rows I agree with sfranklyn on the export to text file and use BCP to import into SQL.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:fatalerror080298
ID: 1499162
There are more then just that one table and I was trying to automate the process a little more.  (Plus I really wanted to learn how to do it in VB if at all possible.)
0
 
LVL 3

Expert Comment

by:jjbyers
ID: 1499163
You could create two different connections (one for each database) in the VB DE and open the tables beneath each one. After this you would use a select insert to move the information from one table to another.

Warning this will be very slooow. You'll be going through two differenct ODBC connections.

My best bet would be to automate the export from access using DAO and then shell out of VB and run the BCP from the command line.
0
 
LVL 3

Expert Comment

by:cognition
ID: 1499164
You should only require a bit of tweaking to get my solution working. That is all in VB, although with a million records, I would also export to text and the use bcp.

If you have SQL7 then use DTS.
0
 
LVL 3

Accepted Solution

by:
cognition earned 250 total points
ID: 1499165
Here is a solution that I have actually run.
It is based on an access db with one table called tableA, with three fields, field1, field2, field3.

You will need to open a new vb project and create a reference to the MS Remote Data Object.
Put a button on the form, and paste the code below into the button snippet.
You will need to 2 ODBC data sources, one called access1, that points at your access mdb, and one called SQLScratch that points at a server and database "Scratch".

You will of course need to create a table in sql server called tableA
create table tablea (field1 varchar(50), field2 varchar(50), field3 varchar(50))

You will need to edit the connection strings, to put in the right dsn's usernames etc.

I have actually run the following and it successfully transferred the records.

Dim Connection1 As rdoConnection
Dim Connection2 As rdoConnection
Dim Environment As rdoEnvironment

Set Environment = rdoEnvironments(0)

' Set up connection1 to access
Set Connection1 = Environment.OpenConnection(dsName:="Access1", Connect:="uid=admin;pwd=", Prompt:=rdDriverNoPrompt)

' Set up connection to sql server
Set Connection2 = Environment.OpenConnection(dsName:="SQLScratch", Connect:="uid=sa;pwd=;database:=Scratch", Prompt:=rdDriverNoPrompt)

Dim rs1 As rdoResultset
Dim rs2 As rdoResultset

' Get tyhe access data
Set rs1 = Connection1.OpenResultset("SELECT * FROM TableA")
' Open a dummy resultset to add records
Set rs2 = Connection2.OpenResultset("SELECT * FROM TableA WHERE 1 = 2", rdOpenDynamic, rdConcurRowVer)

While Not rs1.EOF
    With rs2
       .AddNew
       !field1 = rs1!field1
       !field2 = rs1!field2
       !field3 = rs1!field3
       .Update
    End With
    rs1.MoveNext
Wend

Set rs1 = Nothing
Set rs2 = Nothing
Set Connection1 = Nothing
Set Connection2 = Nothing
Set Environment = Nothing



0
 

Author Comment

by:fatalerror080298
ID: 1499166
Cognition, one of the problems I am having with your code is the following:

Dim Connection1 As rdoConnection
Dim Connection2 As rdoConnection
Dim Environment As rdoEnvironment

rdoConnection and rdoEnvironment do not exist.  There is a connection object but not rdoConnection and can't find anything close to the rdoEnvironment.
0
 

Author Comment

by:fatalerror080298
ID: 1499167
You also use this line:
    Set Environment = rdoEnvironments(0)

There is no rdoEnvironments that I can find.  Is this some user defined type that you have?

0
 
LVL 3

Expert Comment

by:cognition
ID: 1499168
Have you set a reference to Microsoft Remote Data Object under tools|references ?

You have to have VB enterprise to do this. However you can also use DAO, or ADO, but you need to change the code. The above example uses rdo.

0
 
LVL 3

Expert Comment

by:cognition
ID: 1499169
Sorry the reference is set under Project|References menu.
0
 

Author Comment

by:fatalerror080298
ID: 1499170
It is giving me an error at this point:
While Not rs1.EOF
    With rs2
        .AddNew
        !Provider_ID = rs1.[Provider ID]
        !Delegated = rs1.[Delegated]
        !PCP = rs1.[PCP]
        ...
        !Remarks = rs1.[Remarks]
       .Update
    End With

The error that I am getting is:
  Compile Error:
  Method or data member not found.

0
 

Author Comment

by:fatalerror080298
ID: 1499171
The one that it doesn't seem to like is the:
!Provider_ID = rs1.[Provider ID]

The rs1.[Provider ID] is what it highlights as being the problem.
0
 
LVL 3

Expert Comment

by:cognition
ID: 1499172
Use ! not .

rs1![Provider ID]


0
 

Author Comment

by:fatalerror080298
ID: 1499173
I should have figured that one out....  One last thing (hopefully)...  Now it's telling me that the recordset is read only...  How can I take the recordset out of read only mode?
0
 
LVL 3

Expert Comment

by:cognition
ID: 1499174
You need to change the two options in the following line
Set rs2 = Connection2.OpenResultset("SELECT * FROM TableA WHERE 1 = 2", rdOpenDynamic, rdConcurRowVer)

rdOpenDynamic is the type of cursor that is created, and rdConcurRowVer is the type of locking.

Depending on the ODBC driver you are using and which cursor library is used, and the version of SQL server, and where the cursor is created there is a bunch of different combinations.

Cursor Type options :
rdOpenDynamic
rdOpenStatic
rdOpenKeyset
rdOpenForwardOnly (Will allways be readonly)

Lock Type Options :
rdConcurReadOnly (Always read only)
rdConcurRowver
rdConcurValues
rdConcurLock
rdConcurBatch  (For batch updating only)

One of the following should work Try them in reverse order.

rdOpenDynamic, rdConcurRowver
rdOpenDynamic, rdConcurValues
rdOpenStatic, rdConcurRowver
rdOpenStatic, rdConcurValues
rdOpenKeyset, rdConcurRowver
rdOpenKeyset, rdConcurValues


0
 

Author Comment

by:fatalerror080298
ID: 1499175
Thank you very much for your help.  It's there now.  Just trying to work out another problem that I am having but will quit dragging this one on (don't want to tie you up all day on one question).  The only problem I am having now is one of the fields in the first record has a null in it but the receiving field in the SQL server database is a date field and DOES accept NULL values but when the program runs it gives me the following error:
  Run-time error '40060':
  Incorrect type for parameter.

Thanks again for your help!!
0
 
LVL 3

Expert Comment

by:cognition
ID: 1499176
Store the field values to variant type variables, or use the following

rs2!field = IIF(IsNull(rs1!field), "NULL", rs1!field)

or

if not isnull(rs1|field) then
    rs2!field = rs1!field
Endif

instead of just

rs2!field = rs1!field



Thanks for the grade!

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now