• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Linking Excel to Access ADO - Linked table

Clarity

New Member
Hi,


I am fairly new to using VBA in Excel to carry out actions in Access (ADO). I have found a piece of code online to create a DB and then create a standard table.


I want to create a table that is linked to a excel spreadsheet. The code to do this (taken from Access module) is:


DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "CLV_New_test", "C:Documents and SettingsMylesDesktopclvCLV Data Template.xls", True, ""


However when I add this into my code the DB is created successfully but I get an error message on the above line. Runtime error 424 object required. I'm sure I'm missing something simple but after 4 hours of trawling online and through some books I've not come up with anything.


I have Microsoft ActiveX Data Objects Library set up.


Here is my full code:


Sub CreateDatabase()

'Original Author : Ken Puls (www.excelguru.ca)

'Macro Purpose: Create an Access database on the fly

'http://www.excelguru.ca/node/60

Dim Cnct As String, Src As String

Dim Catalog As Object

Dim Connecion As ADODB.Connection

Dim dbPath As String

Dim DB_Name As String

Dim DBFullName As String


DB_Name = Range("DBase_Name").Text

' Database information

If Range("config_DB_InDir").Value = True Then

DBFullName = ThisWorkbook.Path & "" & DB_Name & "Test.mdb"

Else

DBFullName = Range("Database_Path").Text


End If


'Set database name here


Cnct = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFullName & ";"

'Create new database

Set Catalog = CreateObject("ADOX.Catalog")


Catalog.Create Cnct

Set Catalog = Nothing


Set Connection = New ADODB.Connection

Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "

Cnct = Cnct & "Data Source=" & DBFullName & ";"

Connection.Open ConnectionString:=Cnct


DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, "CLV_New_test", "C:Documents and SettingsMylesDesktopclvCLV Data Template.xls", True, ""


Connection.Close

Set Connection = Nothing


End Sub


Any ideas would be greatfully received.


Thanks,


Myles
 
Top