Solved

Access to SQL Server

Posted on 1999-01-18
29
247 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 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