Results 1 to 22 of 22

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

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2004
    Posts
    237

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

    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.
    Last edited by engineer; Aug 9th, 2005 at 10:50 AM.
    software engineer

  2. #2
    Lively Member
    Join Date
    Jan 2002
    Location
    Posts
    114

    Re: Export data from Access table to SQL SERVER table programatically

    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

  3. #3
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: Export data from Access table to SQL SERVER table programatically

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

    something like that...
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,923

    Re: Export data from Access table to SQL SERVER table programatically

    [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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: Export data from Access table to SQL SERVER table programatically

    I thought he wanted one field.. hence the delimeter
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  6. #6
    Lively Member
    Join Date
    Jan 2002
    Location
    Posts
    114

    Re: Export data from Access table to SQL SERVER table programatically

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

  7. #7
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: Export data from Access table to SQL SERVER table programatically

    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

    VB Code:
    1. Option Explicit
    2. Dim cnAccess As ADODB.Connection
    3. Dim rsAccess As ADODB.Recordset
    4. Dim cnMSSQL As ADODB.Connection
    5. Dim rsMSSQL As ADODB.Recordset
    6. Dim a As Long
    7. Dim strDataBaseName As String
    8. Dim strDBCursorType As String
    9. Dim strDBLockType As String
    10. Dim strDBOptions As String
    11. Dim strSQLAccess As String
    12. Dim strSQLMSSQL As String
    13.  
    14. Private Sub Command1_Click()
    15. Dim b As Long
    16.  
    17. Me.MousePointer = 11
    18.  
    19. strDBCursorType = adOpenDynamic
    20. strDBLockType = adLockOptimistic
    21. strDBOptions = adCmdText
    22.  
    23. Set cnAccess = New ADODB.Connection
    24. Set cnMSSQL = New ADODB.Connection
    25.  
    26. cnAccess.Open ConnectString("Access")
    27. cnMSSQL.Open ConnectString("MSSQL")
    28.    
    29.     With cnAccess
    30.         .CommandTimeout = 0
    31.         .CursorLocation = adUseClient
    32.     End With
    33.        
    34.     With cnMSSQL
    35.         .CommandTimeout = 0
    36.         .CursorLocation = adUseClient
    37.     End With
    38.        
    39. Set rsAccess = New ADODB.Recordset       'Creates record set
    40. Set rsMSSQL = New ADODB.Recordset       'Creates record set
    41.              
    42. strSQLAccess = "SELECT * "
    43. strSQLAccess = strSQLAccess & "FROM Table1 "
    44.  
    45. strSQLMSSQL = "SELECT * "
    46. strSQLMSSQL = strSQLMSSQL & "FROM DBO.Table1 "
    47.    
    48. rsAccess.Open strSQLAccess, cnAccess, strDBCursorType, strDBLockType, strDBOptions
    49. rsMSSQL.Open strSQLMSSQL, cnMSSQL, strDBCursorType, strDBLockType, strDBOptions
    50.  
    51. If rsAccess.EOF Or rsAccess.BOF Then
    52.     GoTo ExitSub
    53. Else
    54.     For a = 1 To rsAccess.RecordCount
    55.             rsMSSQL.AddNew
    56.            
    57.             For b = 0 To rsAccess.Fields.Count - 1
    58.                 rsMSSQL.Fields(b).Value = rsAccess.Fields(b).Value
    59.             Next b
    60.            
    61.             rsMSSQL.Update
    62.             rsAccess.MoveNext
    63.     Next a
    64. End If
    65.  
    66. ExitSub:
    67. Me.MousePointer = 0
    68.  
    69. rsAccess.Close
    70. Set rsAccess = Nothing
    71. cnAccess.Close
    72. Set cnAccess = Nothing
    73. rsMSSQL.Close
    74. Set rsMSSQL = Nothing
    75. cnMSSQL.Close
    76. Set cnMSSQL = Nothing
    77.  
    78. End Sub
    79.  
    80. Private Function ConnectString(strDataBaseName As String) As String
    81.  
    82. Dim strDBName As String
    83. Dim strUserID As String
    84. Dim strPassword As String
    85.  
    86.        
    87.         Select Case strDataBaseName
    88.             Case "MSSQL"  'MS SQL DB
    89.                     strDBName = "Database_Name"
    90.                     strUserID = "User_Name"
    91.                     strPassword = "Password"
    92.    
    93.                     ConnectString = "Provider=SQLOLEDB;DATA SOURCE=" & _
    94.                                  "192.168.xxx.xxx;" & _
    95.                                  "Initial Catalog=" & strDBName & _
    96.                                  ";USER ID=" & strUserID & _
    97.                                  ";Password=" & strPassword & ";"
    98.             Case "Access" 'Access Database
    99.                     strDBName = "Path of Access Database" ' i.e. C:\Database\AccessDatabase.mdb
    100.                    
    101.                      ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    102.                                 "Data Source=" & strPath & _
    103.                                 ";Jet OLEDB:Engine Type=5;"
    104.  
    105.         End Select
    106. End Function
    Last edited by Mark Gambo; Aug 8th, 2005 at 09:17 AM.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Sep 2004
    Posts
    237

    Re: Export data from Access table to SQL SERVER table programatically

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

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,923

    Re: Export data from Access table to SQL SERVER table programatically

    Quote Originally Posted by [LGS]Static
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Sep 2004
    Posts
    237

    Angry Re: Export data from Access table to SQL SERVER table programatically

    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?
    software engineer

  11. #11
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: Export data from Access table to SQL SERVER table programatically

    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:

    VB Code:
    1. With cnAccess
    2.         .CommandTimeout = 0
    3.         .CursorLocation = [COLOR=Red]adUseServer[/COLOR] 'adUseClient    
    4.     End With
    5.        
    6.     With cnMSSQL
    7.         .CommandTimeout = 0
    8.         .CursorLocation = [COLOR=Red]adUseServer[/COLOR] 'adUseClient
    9.     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
    Last edited by Mark Gambo; Aug 8th, 2005 at 03:44 PM.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Sep 2004
    Posts
    237

    Re: Export data from Access table to SQL SERVER table programatically

    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.
    software engineer

  13. #13
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: Export data from Access table to SQL SERVER table programatically

    Quote Originally Posted by 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.
    I would try either an "NVARCHAR" or a "TEXT" field type.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  14. #14
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,923

    Re: Export data from Access table to SQL SERVER table programatically

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Export data from Access table to SQL SERVER table programatically

    (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.

  16. #16
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: Export data from Access table to SQL SERVER table programatically

    Quote Originally Posted by techgnome
    . . . 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
    Attached Images Attached Images  
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  17. #17
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,923

    Re: Export data from Access table to SQL SERVER table programatically

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    Sep 2004
    Posts
    237

    Re: Export data from Access table to SQL SERVER table programatically

    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.
    software engineer

  19. #19
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: Export data from Access table to SQL SERVER table programatically

    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.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  20. #20

    Thread Starter
    Addicted Member
    Join Date
    Sep 2004
    Posts
    237

    Re: Export data from Access table to SQL SERVER table programatically

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

  21. #21
    PowerPoster
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    2,351

    Re: Export data from Access table to SQL SERVER table programatically

    Quote Originally Posted by Mark Gambo View Post
    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

    VB Code:
    1. Option Explicit
    2. Dim cnAccess As ADODB.Connection
    3. Dim rsAccess As ADODB.Recordset
    4. Dim cnMSSQL As ADODB.Connection
    5. Dim rsMSSQL As ADODB.Recordset
    6. Dim a As Long
    7. Dim strDataBaseName As String
    8. Dim strDBCursorType As String
    9. Dim strDBLockType As String
    10. Dim strDBOptions As String
    11. Dim strSQLAccess As String
    12. Dim strSQLMSSQL As String
    13.  
    14. Private Sub Command1_Click()
    15. Dim b As Long
    16.  
    17. Me.MousePointer = 11
    18.  
    19. strDBCursorType = adOpenDynamic
    20. strDBLockType = adLockOptimistic
    21. strDBOptions = adCmdText
    22.  
    23. Set cnAccess = New ADODB.Connection
    24. Set cnMSSQL = New ADODB.Connection
    25.  
    26. cnAccess.Open ConnectString("Access")
    27. cnMSSQL.Open ConnectString("MSSQL")
    28.    
    29.     With cnAccess
    30.         .CommandTimeout = 0
    31.         .CursorLocation = adUseClient
    32.     End With
    33.        
    34.     With cnMSSQL
    35.         .CommandTimeout = 0
    36.         .CursorLocation = adUseClient
    37.     End With
    38.        
    39. Set rsAccess = New ADODB.Recordset       'Creates record set
    40. Set rsMSSQL = New ADODB.Recordset       'Creates record set
    41.              
    42. strSQLAccess = "SELECT * "
    43. strSQLAccess = strSQLAccess & "FROM Table1 "
    44.  
    45. strSQLMSSQL = "SELECT * "
    46. strSQLMSSQL = strSQLMSSQL & "FROM DBO.Table1 "
    47.    
    48. rsAccess.Open strSQLAccess, cnAccess, strDBCursorType, strDBLockType, strDBOptions
    49. rsMSSQL.Open strSQLMSSQL, cnMSSQL, strDBCursorType, strDBLockType, strDBOptions
    50.  
    51. If rsAccess.EOF Or rsAccess.BOF Then
    52.     GoTo ExitSub
    53. Else
    54.     For a = 1 To rsAccess.RecordCount
    55.             rsMSSQL.AddNew
    56.            
    57.             For b = 0 To rsAccess.Fields.Count - 1
    58.                 rsMSSQL.Fields(b).Value = rsAccess.Fields(b).Value
    59.             Next b
    60.            
    61.             rsMSSQL.Update
    62.             rsAccess.MoveNext
    63.     Next a
    64. End If
    65.  
    66. ExitSub:
    67. Me.MousePointer = 0
    68.  
    69. rsAccess.Close
    70. Set rsAccess = Nothing
    71. cnAccess.Close
    72. Set cnAccess = Nothing
    73. rsMSSQL.Close
    74. Set rsMSSQL = Nothing
    75. cnMSSQL.Close
    76. Set cnMSSQL = Nothing
    77.  
    78. End Sub
    79.  
    80. Private Function ConnectString(strDataBaseName As String) As String
    81.  
    82. Dim strDBName As String
    83. Dim strUserID As String
    84. Dim strPassword As String
    85.  
    86.        
    87.         Select Case strDataBaseName
    88.             Case "MSSQL"  'MS SQL DB
    89.                     strDBName = "Database_Name"
    90.                     strUserID = "User_Name"
    91.                     strPassword = "Password"
    92.    
    93.                     ConnectString = "Provider=SQLOLEDB;DATA SOURCE=" & _
    94.                                  "192.168.xxx.xxx;" & _
    95.                                  "Initial Catalog=" & strDBName & _
    96.                                  ";USER ID=" & strUserID & _
    97.                                  ";Password=" & strPassword & ";"
    98.             Case "Access" 'Access Database
    99.                     strDBName = "Path of Access Database" ' i.e. C:\Database\AccessDatabase.mdb
    100.                    
    101.                      ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    102.                                 "Data Source=" & strPath & _
    103.                                 ";Jet OLEDB:Engine Type=5;"
    104.  
    105.         End Select
    106. 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?

  22. #22
    New Member
    Join Date
    Jul 2010
    Posts
    1

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width