[RESOLVED]: Export data from Access table to SQL SERVER table programatically

[engineer]

Dear All,

I have a table in Access. I want to export the data from that table's fields into one field of SQL SERVER database table. for eaxmple.

Let I have a table named A in access. Table A has 5 fields. Also i have a table in SQL SQRVER.Let that table name is B. I want to export data from Access table A fileds into one field of SqlServer table B.

Can you help me plz.

[poison6feet]

set 2 connections - one Connection with Access Database and One with SQL Database.
Set 2 record set - Recordset1 with Access and RecordSet2 with SQL.
Put One button - On click of this button Update the Data from RecordSet1 to RecordSet2....
Hope this will work... if works fine please rate my reply

[Static]

INSERT INTO B (FIELD1) SELECT [Field1] & ";" & [Field2] & ";" & [Field3] & ";" & [Field4] & ";" & [Field5] FROM A

something like that...

[techgnome]

[LGS]Static -- that's not going to work.... it's bad syntax.
The ; between fields names should be , instead.

--- oh, wait.... I see ..... depending on the desired result, then yes, that could work.

engineer - is you intention to put all 5 Access fields into ONE SQL field? OR Field1 = Field1, Field2 = Field2....???


Tg

[Static]

I thought he wanted one field.. hence the delimeter

[poison6feet]

But here My earlier post will be work in either case ... all fields from access into one field in sql server or one to one.....
what do u think guys...

[Mark Gambo]

Create two recordsets (One for Access and One of MS SQL) and then copy the contents from on recordset to the other. Try this code

Option Explicit
Dim cnAccess As ADODB.Connection
Dim rsAccess As ADODB.Recordset
Dim cnMSSQL As ADODB.Connection
Dim rsMSSQL As ADODB.Recordset
Dim a As Long
Dim strDataBaseName As String
Dim strDBCursorType As String
Dim strDBLockType As String
Dim strDBOptions As String
Dim strSQLAccess As String
Dim strSQLMSSQL As String
 
Private Sub Command1_Click()
Dim b As Long
 
Me.MousePointer = 11
 
strDBCursorType = adOpenDynamic
strDBLockType = adLockOptimistic
strDBOptions = adCmdText
 
Set cnAccess = New ADODB.Connection
Set cnMSSQL = New ADODB.Connection
 
cnAccess.Open ConnectString("Access")
cnMSSQL.Open ConnectString("MSSQL")
    
    With cnAccess
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With
        
    With cnMSSQL
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With
        
Set rsAccess = New ADODB.Recordset       'Creates record set
Set rsMSSQL = New ADODB.Recordset       'Creates record set
             
strSQLAccess = "SELECT * "
strSQLAccess = strSQLAccess & "FROM Table1 "
 
strSQLMSSQL = "SELECT * "
strSQLMSSQL = strSQLMSSQL & "FROM DBO.Table1 "
    
rsAccess.Open strSQLAccess, cnAccess, strDBCursorType, strDBLockType, strDBOptions
rsMSSQL.Open strSQLMSSQL, cnMSSQL, strDBCursorType, strDBLockType, strDBOptions
 
If rsAccess.EOF Or rsAccess.BOF Then
    GoTo ExitSub
Else
    For a = 1 To rsAccess.RecordCount
            rsMSSQL.AddNew
            
            For b = 0 To rsAccess.Fields.Count - 1
                rsMSSQL.Fields(b).Value = rsAccess.Fields(b).Value
            Next b
            
            rsMSSQL.Update
            rsAccess.MoveNext
    Next a
End If
 
ExitSub:
Me.MousePointer = 0
 
rsAccess.Close
Set rsAccess = Nothing
cnAccess.Close
Set cnAccess = Nothing
rsMSSQL.Close
Set rsMSSQL = Nothing
cnMSSQL.Close
Set cnMSSQL = Nothing
 
End Sub
 
Private Function ConnectString(strDataBaseName As String) As String
 
Dim strDBName As String
Dim strUserID As String
Dim strPassword As String
 
        
        Select Case strDataBaseName
            Case "MSSQL"  'MS SQL DB
                    strDBName = "Database_Name"
                    strUserID = "User_Name"
                    strPassword = "Password"
    
                    ConnectString = "Provider=SQLOLEDB;DATA SOURCE=" & _
                                 "192.168.xxx.xxx;" & _
                                 "Initial Catalog=" & strDBName & _
                                 ";USER ID=" & strUserID & _
                                 ";Password=" & strPassword & ";"
            Case "Access" 'Access Database
                    strDBName = "Path of Access Database" ' i.e. C:\Database\AccessDatabase.mdb
                    
                     ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=" & strPath & _
                                ";Jet OLEDB:Engine Type=5;"
 
        End Select
End Function

[engineer]

Thankx guyz for your quick response.
I am gona try the solution given by you.

[techgnome]

I thought he wanted one field.. hence the delimeter

Well, yeah, I see that NOOOW.... but you know as well as I that 90% of the time what was asked for isn't what the user wanted.... sometime you have to read between the lines and fake it.

;P

Tg

[engineer]

I have Tried Mark's code with some alteration. Now it inserts the records successfully in sql table, But for some of the records it gives me the following error on rsMSSQL.Update

ERROR:

Data provider or other service returned an E_FAIL status

Can you guide me what does this error mean?

[Mark Gambo]

What Version of MDAC 2.x are you using? Are you trying insert a date? If so, use a server side cursor by changing the following code:

With cnAccess
        .CommandTimeout = 0
        .CursorLocation = [COLOR=Red]adUseServer[/COLOR] 'adUseClient     
    End With
        
    With cnMSSQL
        .CommandTimeout = 0
        .CursorLocation = [COLOR=Red]adUseServer[/COLOR] 'adUseClient
    End With

Useful Links:

http://forums.devshed.com/archive/t-83849
http://support.microsoft.com/kb/q198532/
http://forums.devshed.com/showthread...5629&forumid=4
http://forums.devshed.com/showthread.php?threadid=17034&forumid=4

[engineer]

Dear mark,

What I have observer so far is that the error comes if i try to insert the data having length > 8000 in the sql table fields( varchar).

The Max length of the varchar data type field is 8000, so when i try to insert the data having length > 8000, the error comes.

I am going to tackel with it by breakinmg the record in to two records, as it does not effect in my scenario.

Thankx for your help.

[Mark Gambo]

Dear mark,

What I have observer so far is that the error comes if i try to insert the data having length > 8000 in the sql table fields( varchar).

The Max length of the varchar data type field is 8000, so when i try to insert the data having length > 8000, the error comes.

I am going to tackel with it by breakinmg the record in to two records, as it does not effect in my scenario.

Thankx for your help.

I would try either an "NVARCHAR" or a "TEXT" field type.

[techgnome]

Unless you are storing unitcode, nvarchar isn't going to be any better (and "Text" doesn't exist as a type in SQL Server).... SQL Server has a row data limit of 8k. That would be 8k characters ASCII or 4k characters unicode. That's all there is to it. So if you need something longer than that, it needs to be broken up over multiple rows, or multiple tables.

There is one alternative to that, and it involves using an image datatype and storing the binary version of the data, but it becomes much harder to extract.

Tg

[brucevde]

(and "Text" doesn't exist as a type in SQL Server)....

???

There is a Text datatype is SQL Server, it can store 2,147,483,647 characters.

The NText can store 1,073,741,823 characters.

[Mark Gambo]

. . . and "Text" doesn't exist as a type in SQL Server . . .

You should check out this link. I have a TEXT, nTEXT data types in my copy of MS SQL

[techgnome]

Well, I'll be.

I'll take that humble pie a la mode if you don't mind. You learn some thing new avery day.

Ok, so given varchar, nvarchar, char and nchar.... what use is "text"?

Tg

[engineer]

hummmmm
It means I can use Text data type and then there would be no need of splitting the record in two records(when > 8000).

I am gona try this one.

Thankx you very much dear.

[Mark Gambo]

According to MSDN:

ntext, text, and image
Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.

ntext

Variable-length Unicode data with a maximum length of 230 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-92 synonym for ntext is national text.

text

Variable-length non-Unicode data in the code page of the server and with a maximum length of 231-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

image

Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.

[engineer]

Thankx to Guyz.
Thankx alot for helping and guiding me.

[luca90]

Create two recordsets (One for Access and One of MS SQL) and then copy the contents from on recordset to the other. Try this code

Option Explicit
Dim cnAccess As ADODB.Connection
Dim rsAccess As ADODB.Recordset
Dim cnMSSQL As ADODB.Connection
Dim rsMSSQL As ADODB.Recordset
Dim a As Long
Dim strDataBaseName As String
Dim strDBCursorType As String
Dim strDBLockType As String
Dim strDBOptions As String
Dim strSQLAccess As String
Dim strSQLMSSQL As String
 
Private Sub Command1_Click()
Dim b As Long
 
Me.MousePointer = 11
 
strDBCursorType = adOpenDynamic
strDBLockType = adLockOptimistic
strDBOptions = adCmdText
 
Set cnAccess = New ADODB.Connection
Set cnMSSQL = New ADODB.Connection
 
cnAccess.Open ConnectString("Access")
cnMSSQL.Open ConnectString("MSSQL")
    
    With cnAccess
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With
        
    With cnMSSQL
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With
        
Set rsAccess = New ADODB.Recordset       'Creates record set
Set rsMSSQL = New ADODB.Recordset       'Creates record set
             
strSQLAccess = "SELECT * "
strSQLAccess = strSQLAccess & "FROM Table1 "
 
strSQLMSSQL = "SELECT * "
strSQLMSSQL = strSQLMSSQL & "FROM DBO.Table1 "
    
rsAccess.Open strSQLAccess, cnAccess, strDBCursorType, strDBLockType, strDBOptions
rsMSSQL.Open strSQLMSSQL, cnMSSQL, strDBCursorType, strDBLockType, strDBOptions
 
If rsAccess.EOF Or rsAccess.BOF Then
    GoTo ExitSub
Else
    For a = 1 To rsAccess.RecordCount
            rsMSSQL.AddNew
            
            For b = 0 To rsAccess.Fields.Count - 1
                rsMSSQL.Fields(b).Value = rsAccess.Fields(b).Value
            Next b
            
            rsMSSQL.Update
            rsAccess.MoveNext
    Next a
End If
 
ExitSub:
Me.MousePointer = 0
 
rsAccess.Close
Set rsAccess = Nothing
cnAccess.Close
Set cnAccess = Nothing
rsMSSQL.Close
Set rsMSSQL = Nothing
cnMSSQL.Close
Set cnMSSQL = Nothing
 
End Sub
 
Private Function ConnectString(strDataBaseName As String) As String
 
Dim strDBName As String
Dim strUserID As String
Dim strPassword As String
 
        
        Select Case strDataBaseName
            Case "MSSQL"  'MS SQL DB
                    strDBName = "Database_Name"
                    strUserID = "User_Name"
                    strPassword = "Password"
    
                    ConnectString = "Provider=SQLOLEDB;DATA SOURCE=" & _
                                 "192.168.xxx.xxx;" & _
                                 "Initial Catalog=" & strDBName & _
                                 ";USER ID=" & strUserID & _
                                 ";Password=" & strPassword & ";"
            Case "Access" 'Access Database
                    strDBName = "Path of Access Database" ' i.e. C:\Database\AccessDatabase.mdb
                    
                     ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=" & strPath & _
                                ";Jet OLEDB:Engine Type=5;"
 
        End Select
End Function

Hi Mark sorry if i post on a old thread...

I need the same code but reversed idea Sql>accees, how to modify you code?

[furqi24]

HI 2 ALL.......
nice work done by all of u........