[AccessD] Urgent: ADO Recordset from SQL to Access DB

jeffrey.demulling at usbank.com jeffrey.demulling at usbank.com
Tue Apr 13 08:54:58 CDT 2004


Here is some code.

I use a module that contains my connection information:

Function ODRConnection(myid As String, mypassword As String)
    ODRConnection = "Provider=SQLOLEDB.1;Password=" & mypassword &
";Persist Security Info=True;User ID=" & myid & ";Initial Catalog=" &
GetSEIDatabase & ";Data Source=" & GetSEIServerName
End Function

Now to open the recordset ad append the records:

Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmdtext As String

con = setconnection
    con.Open

    cmdtext = "INSERT INTO tblProviders ("
    cmdtext = cmdtext & " [ProviderName] )"
    cmdtext = cmdtext & " VALUES ("
    cmdtext = cmdtext & " '" & Me.txtCustomerName & "');"

    rs.Open cmdtext, con

    con.Close

set rs = nothing
set con = nothing


                                                                                                                                               
                      ACTEBS                                                                                                                   
                      <actebs at actebs.com.au>           To:       "access group" <accessd at databaseadvisors.com>                                 
                      Sent by:                         cc:                                                                                     
                      accessd-bounces at databasea        Subject:  [AccessD] Urgent: ADO Recordset from SQL to Access DB                         
                      dvisors.com                                                                                                              
                                                                                                                                               
                                                                                                                                               
                      04/13/2004 08:33 AM                                                                                                      
                      Please respond to "Access                                                                                                
                      Developers discussion and                                                                                                
                      problem solving"                                                                                                         
                                                                                                                                               
                                                                                                                                               




Hi Everyone,

I am onsite doing some coding for a client and don't have access to a
couple
of files/examples at my office. If anyone has some code to connect to an
SQL
Server DB via ADO then open the recordset and then append the records to an
Access DB table I would be forever grateful. I have been working on this
project all day (interstate) and this is the last of it, but for the life
of
me I can't work out how to set it up...I know I could setup and ODBC
connection, but that's not an elegant solution...

Any help will be much appreciated...

Regards
Vlad


--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com









More information about the AccessD mailing list