Using Excel VBA code through a SQL Server 2008 Database

[Benjaminvh]

Hi All

I have a table in SQL Server, call it table1, residing in a database, db1.
I have excel VBA code that was originally designed to run through a table in Access. The code performs a bunch of calculations, produces a few records based on the data in the table and the calculations, and puts these new records in another table.

I moved to SQL Server since Access' storage limitations were exceeded.

What I need help with is modifying my code to deal with SQL Server. Note, I don't want my code transferred from Excel VBA to SQL. What I want is to change this:

Set TempDBse = OpenDatabase([ExpPath] & [ExpFile_Temp])
Set TempTable = TempDBse.OpenRecordset([ExpTable_Temp], dbOpenTable)

which works to 'establish a connection' with an access table, into something that establishes a connection with table1 in db1 in SQL Server. I am new to this, so please be gentle (e.g. I don't understand the difference between ADO and DAO etc).

I found something like:

Dim MyConnObj As New ADODB.Connection
Dim myRecSet As New ADODB.Recordset

but what should follow this? In case this helps, when I open SQL Server 2008 I get the following screen:

Server Type: Database Engine
Server Name: OM020084
Authentication: Windows Authentication
Username: OMCORE/om85558
Password:

(Username and Password are both 'greyed out' i.e. I can't change these fields; password is blank).

Thanks a million
Ben

 

[ByteMyzer]

Check out the following:

http://www.connectionstrings.com/sql-server-2008