VBA import excel to SQL Server 2

[osx99]

The following code sits in an excel spreadsheet and opens an access database to insert a new record. I want to replicate this but to import into an identical SQL Server database instead.

Sub DAOFromExcelToAccess()

Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("C:\Documents and Settings\O\Desktop\RPData.mdb")

    Set rs = db.OpenRecordset("thursdaytable", dbOpenTable)

    Set rs2 = db.OpenRecordset("SELECT Max([Race Number]) FROM thursdaytable")

        With rs
            .AddNew ' create a new record

            .Fields("Race Number") = rs2.Fields(0) + 1
            .Fields("osknows1") = Range("a4").Value
            .Fields("osknows2") = Range("a5").Value
            .Fields("buttonhole") = Range("b6").Value

            .Update ' stores the new record
        End With

    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Is anyone able to let me know how I can do the same with an identical table in SQL server with filename - C:\Documents and Settings\O\Desktop\RPData.mdf?

Many thanks,
Os

 

[ettienne]

You have to either open the SQL database in your code (you cannot open the .mdf file directly), or attach the SQL table to your Access database and use your existing code.

Search for DAO SQL connection string.

 

[chwrig]

I have a similar task with exporting an MS Access database to MySQL db. Is it possible to do that automatically or do I have to write code for exporting the Access DB to an SQL/CSV file first (which can be imported to MySQL via the phpmyadmin tool, for example). Any help is appreciated.

 

[xlbo]

Easiest way to do this is via ADO:
Sub Update()
'Set reference to ADO object library
Dim strCon As ADODB.Connection
Dim recSet As ADODB.Recordset

strSQL = "Insert SQL goes here - just build the string using range references as appropriate"

Set strCon = New ADODB.Connection

strCon.Open "Connection string goes here"

Set recSet = strCon.Execute(strSQL)
End Sub


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[osx99]

Thanks for the pointers guys. I found this which is quite useful

http://www.connectionstrings.com/