What's wrong with my SQL code?

[azizrasul]

How do I change the following SQL statement (I get an error on Sheet1$DA1)?

    strSQL = "INSERT INTO [" & strTablename & "] ([username]) " _
    & "SELECT F1 As username FROM " _
    & "[Excel 8.0;HDR=NO;DATABASE=" & ActiveWorkbook.FullName & "].[Sheet1$DA1]"

 

[Kyle123]

What's the error you get? Also you shouldn't be querying the active open workbook, it causes memory leaks

 

[azizrasul]

The error is that the MS Access database engine can't find the object Sheet1$DA1. The data is supposed to go a MS Access table (strTablename).

The code only activates when the Workbook opens.

 

[azizrasul]

strSQL = "INSERT INTO [" & strTablename & "] ([username]) " _
    & "SELECT F1 As username FROM " _
    & "[Excel 8.0;HDR=NO;DATABASE=" & ActiveWorkbook.FullName & "].[Sheet1$DA1:DA1]"

seems to work. Hmmmm.

 

[Kyle123]

Glad you got it sorted, but ActiveWorkbook.FullName means you are querying the active workbook - this causes memory leaks in conjunction with ado

 

[azizrasul]

Basically the Active Workbook can only be used by 1 person at a time. So what I was trying to do was that when the workbook opened it transferred the username to Access db which needs to append data to the workbook. Once the code has run, then that's it.

If there is another way of accomplishing this task without memory leaks, I would be interested.

 

[Kyle123]

Something like (access syntax isn't my thing, so it might need tweaking):

strSQL = "INSERT INTO [" & strTablename & "] ([username]) Values ('" & ActiveWorkbook.Sheet1.Range("DA1").value &"');"

 

[azizrasul]

I get error 438 - Object doesn't support this property or method.

 

[Kyle123]

typo

strSQL = "INSERT INTO [" & strTablename & "] ([username]) Values ('" & activeworkbook.Sheets("sheet1").Range("DA1").value &"');"

 

[azizrasul]

That did it. Many thanks for your perseverence.