Sample Codes for SQL for Excel VBA

[Sandra Poh]

Hi, currently, I am using Excel 2000. I would like to do the below in VBA. I
am new in SQL Programming in VBA. To help you to understand myself better, I
have been programming in VBA for Excel quite sometimes. But for the below, I
am totally new.
Pls help me. It is urgent.

01) Check that a local pc connected to an SQL Server (This server is located
in my
office) successfully.
02) Connect to the SQL Server.
03) Open the database.
04) Open a table in this database.
05) Extract this table.
06) Copy all extracted records and paste in one worksheet in my Excel File.
07) Close the table.
08) Close the database.
09) Close the connection.

You may reply to my below email addresses.

01) sandrapoh@hotmail.com
02) sandrapoh@yahoo.com

Thanks and Best Regards
Sandra Poh

[Tom Ogilvy]

Fortunately, you are not the first:

http://tinyurl.com/kac4o

--
Regards,
Tom Ogilvy


"Sandra Poh" wrote:

>
> Hi, currently, I am using Excel 2000. I would like to do the below in VBA. I
> am new in SQL Programming in VBA. To help you to understand myself better, I
> have been programming in VBA for Excel quite sometimes. But for the below, I
> am totally new.
> Pls help me. It is urgent.
>
> 01) Check that a local pc connected to an SQL Server (This server is located
> in my
> office) successfully.
> 02) Connect to the SQL Server.
> 03) Open the database.
> 04) Open a table in this database.
> 05) Extract this table.
> 06) Copy all extracted records and paste in one worksheet in my Excel File.
> 07) Close the table.
> 08) Close the database.
> 09) Close the connection.
>
> You may reply to my below email addresses.
>
> 01) sandrapoh@hotmail.com
> 02) sandrapoh@yahoo.com
>
> Thanks and Best Regards
> Sandra Poh

[Sandra Poh]

Dear Tom,
Thanks. From your hyperlink, I have tried to go thru the thread and did a
sample code in the VBA of a new Excel workbook as shown below.


/////////Start of Code
Sub Test()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim shtCopy As Worksheet
Set shtCopy = ThisWorkbook.Worksheets("MOS Material Master")

'cn.Open "Provider=SQLOLEDB;Data Source=FRKfwd03;" & "Initial
Catalog=ORS2K;UID=SA;PWD=;"
'rs.Open "tbl1", conn, adOpenDynamic, adLockPessimistic

cn.Open "Provider=SQLOLEDB;Data Source=MOS;" & "Initial
Catalog=MOS;UID=sandra;PWD=;"
rs.Open "Product", conn, adOpenDynamic, adLockPessimistic

shtCopy.Activate
shtCopy.Range("A1").CopyFromRecordset rs

End Sub

End of Code////////////


Questions
======
01) When I tried to run the program, it ends with the the below error
message. I am
not sure which part of my code have when wrong. I have tried this when
running
the program in my office where the SQL server is.

Run-time error '-2147467259 (80004005);
Automation error
Unspecified error

02) As per the above source code, I am not sure of the below part. Pls
explain.
"Provider=SQLOLEDB;Data Source=MOS;" & "Initial
Catalog=MOS;UID=sandra;PWD=;"

I need guidance urgently.

[Sandra Poh]

Dear Tom,
Thanks. From your hyperlink, I have tried to go thru the thread and did a
sample code in the VBA of a new Excel workbook as shown below.


/////////Start of Code
Sub Test()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim shtCopy As Worksheet
Set shtCopy = ThisWorkbook.Worksheets("MOS Material Master")

'cn.Open "Provider=SQLOLEDB;Data Source=FRKfwd03;" & "Initial
Catalog=ORS2K;UID=SA;PWD=;"
'rs.Open "tbl1", conn, adOpenDynamic, adLockPessimistic

cn.Open "Provider=SQLOLEDB;Data Source=MOS;" & "Initial
Catalog=MOS;UID=sandra;PWD=;"
rs.Open "Product", conn, adOpenDynamic, adLockPessimistic

shtCopy.Activate
shtCopy.Range("A1").CopyFromRecordset rs

End Sub

End of Code////////////


Questions
======
01) When I tried to run the program, it ends with the the below error
message. I am
not sure which part of my code have when wrong. I have tried this when
running
the program in my office where the SQL server is.

Run-time error '-2147467259 (80004005);
Automation error
Unspecified error

02) As per the above source code, I am not sure of the below part. Pls
explain.
"Provider=SQLOLEDB;Data Source=MOS;" & "Initial
Catalog=MOS;UID=sandra;PWD=;"

I need guidance urgently.