Benjaminvh
Registered User.
- Local time
- Today, 19:21
- Joined
- Mar 11, 2009
- Messages
- 17
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
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

