CODEWITHSUNDEEP
excelms-accessvba
Robert Kendall
Robert Kendall

Reputation: 390

Connection String between Excel and access

This sub is run in Access to import data from an Excel file, "FileName"

This connection string/open procedure is pretty much copied from MSDN references, but there is an error.

When I step through, I receive "unrecognized database format" on the oConn.Open line.

Filename is a spreadsheet, not a database. How do I indicate this?

Public Sub Import2(FileName As Variant)
    Dim wb As Object, ws As Object
    Dim xl As Object
    Set xl = CreateObject("excel.Application")
    Dim qs As String
    Dim ValueString As String
    Dim sConn As String

    Dim oConn As Object
    Set oConn = CreateObject("adodb.connection")

    oConn.ConnectionString = "Provider=microsoft.ACE.OLEDB.12.0; Data Source=filename; Extended Properties=Excel 12.0 XML; hdr=yes" & ";"
    oConn.Open FileName

    Set wb = xl.Workbooks.Open(FileName)
    Set ws = wb.worksheets("For Export")
    data1 = ws.cells(2, 1)
    Data2 = ws.cells(2, 2)
    Data3 = ws.cells(2, 3)

    'Following lines may or may not be correct.  Working on the connection string
    'ValueString = Format(data1, "yyyy\/mm\/dd")
    'qs = "INSERT INTO MAF (FormDate) VALUES (#" & ValueString & "#)"

    DoCmd.RunSQL (qs)
    'CurrentDb.Execute qs

End Sub

Upvotes: 0

Views: 866

Answers (1)

bdn02
bdn02

Reputation: 1500

Change Data Source=filename with something like Data Source=c:\test\db1.mdb with the path of your file

Upvotes: 1

Related Questions