Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 23rd, 2006, 11:57 AM
Registered User
 
Join Date: Jun 2006
Location: Stanford, CA, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA Data Source error

Hi to all the experts:D
I have the following Data Source in ADODB.Connection that is fully functional:

Code:
"Data Source=D:\Book1.xls;" & _ 
"Extended Properties=Excel 8.0"
In the following ADODB.Connection:
Code:
Sub DB_con1() 


    Dim cn As ADODB.Connection 
    Dim strSQL As String 
    Dim lngRecsAff As Long 
    On Error Goto test_Error 

    Set cn = New ADODB.Connection 
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=D:\Book1.xls;" & _ 
    "Extended Properties=Excel 8.0" 

     'Import by using Jet Provider.
    strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _ 
    "Server=mydbserver;Database=DEV;" & _ 
    "UID=sa;PWD=Welcome1@].abk_import " & _ 
    "Select * FROM [Sheet1$]" 
    Debug.Print strSQL 
    cn.Execute strSQL, lngRecsAff ', adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff 

    cn.Close 
    Set cn = Nothing 

    On Error Goto 0 
    Exit Sub 

test_Error: 

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of VBA Document ThisWorkbook" 

End Sub 
 ''''''''''''''''''''''''''''''''''''''''''''

To avoid putting the full path in Data Source for the excel file, I changed :


Code:
    "Data Source=D:\Book1.xls;" & _ 
    "Extended Properties=Excel 8.0"


to

Code:
 "Data Source=" & App.Path & "\ Book1.xls;" & _ 
    "Extended Properties=Excel 8.0"



Then, when I ran the code, I get the following error:
Code:
Error 42(Object required) in procedure test of VBA Document ThisWorkbook.

I have been unfortunate to find an answer for this problem.

Has anyone seen this error and find any resolution for it?

Thanks for any help.

Abrahim


 
Old June 26th, 2006, 01:30 PM
Authorized User
 
Join Date: Mar 2006
Location: London, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I presume you have defined what App.Path is.





Similar Threads
Thread Thread Starter Forum Replies Last Post
SOLVED - VBA Help For Row Source and Record Source eusanpe Access VBA 4 May 13th, 2008 11:58 AM
Cannot create a connection to data source error mojo1979 BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 April 22nd, 2008 10:24 AM
Object Data Source error shoakat ASP.NET 2.0 Professional 1 June 20th, 2007 05:40 PM
Object Data Source Error abupapa ASP.NET 2.0 Basics 1 June 13th, 2007 05:43 AM
Error: Data source name not found and no default d Justine Classic ASP Professional 1 January 23rd, 2004 11:34 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.