Runtime error - " is not a name

[vza]

I want to create a program which transfers all data from a selected Excel Spreadsheet (selected ussing the CommonDialog Control) to an Access DB (also selected using a CommonDialog Control). Here is the code I have for my "Convert" button:

Private Sub ConvertButton_Click()
    Dim CnXL As New ADODB.Connection
        CnXL.Provider = "Microsoft.Jet.OLEDB.4.0"
        CnXL.ConnectionString = "Data Source = '" & ExcelText.Text & "'; Extended Properties = Excel 8.0"
        CnXL.Open
        
        CnXL.Execute "SELECT * INTO [CONVERSIONS] IN '" & AccessText.Text & "' FROM [Sheet1$]"
        
        CnXL.Close
        
End Sub
[\code]

ExcelText and AccessText are textboxes containing the paths to the Excel Spreadsheet and Access Database respectively. An error arises at the Execute statement which gives a runtime error saying:
     Run-time Error '-2147467259 (80004005)':
     " is not a name. Make sure that it does not include invalid characters or punctuation and that it is not too long.  

I am not really sure if ["] is one double quote (") or 2 single quotes ('').  Could this be because the spreadsheet contains null values??  Any responses would be greatly appreciated.

Thanks
-vza

 

[AndyWatt]

What does AccessText.Text contain?

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"A computer program does what you tell it to do, not what you want it to do." -- Greer's Third Law

 

[ca8msm]

If it is a case of having apostrophes try using the replace command to "double them up". e.g.

CnXL.Execute "SELECT * INTO [CONVERSIONS] IN '" & Replace(AccessText.Text,"'","''") & "' FROM [Sheet1$]"

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[vza]

Andy
the AcessText contains the path to the Access Database..its location.

ca8msm
i will try the replace and get back to you

Thaks for such hasty responses!

-vza

 

[vza]

I tried the replace function and still recieved same error.
I appreciate the help however...i never knew a Replace function even existed!

THanks
-vza

 

[nicsin]

Have you checked if the fields that are null in the spreadsheet are nullable in the database?

 

[ca8msm]

OK try this this and lets see what it is trying to insert:

Private Sub ConvertButton_Click()
    'Dim CnXL As New ADODB.Connection
        'CnXL.Provider = "Microsoft.Jet.OLEDB.4.0"
        'CnXL.ConnectionString = "Data Source = '" & ExcelText.Text & "'; Extended Properties = Excel 8.0"
        'CnXL.Open
        
        'CnXL.Execute "SELECT * INTO [CONVERSIONS] IN '" & AccessText.Text & "' FROM [Sheet1$]"
        
        'CnXL.Close
        Debug.Print AccessText.Text
End Sub

Press Ctrl-G to open your immediate window and then run your program and click the convert button. Copy your sql statement and post it back here and we'll see if we can help.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[nicsin]

ca8msm,

I think AccessText.Text only holds the path to the db. I don't think that there is a way to actually see the sql... My guess is that vza gets the error because null values are trying to be inserted to not nullable fields!

vza,

are the fields of the database nullable or not nullable? You may have to convert them to nullable if there are nulls in the excel sheet.

 

[vza]

it prints out the string:

D:\Program Files\FSApp\db.mdb

Which is what is should be...this is where the db is located

-vza

 

[ca8msm]

nicsin...I think you are right...I just wanted to make sure there was nothing in the sql statement first.

vsa...try nicsin's suggestion and let us know what happens.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[vza]

I think I figured it out by altering my SQL statement:

CnXL.Execute "INSERT INTO [CONVERSIONS] IN '" & AccessText.Text & "' SELECT * FROM [Sheet1$]"
[\code]

But...it inserts the data into a NEW WORKSHEET instead of the Access Database....why is the IN statement being ignored?  Also how would I select a range of the worksheet??

Thanks
-vza