asked on

excel vba connect to sql server 2005 express

Hello to the team again.
When I'm Using excel VBA to load sql server 2005 express data from tables that populate manually, with Management Studio Express. It works fine.
When I added new DB and populate it with my tables from access, using ODBC. it's not work.
I 'm getting an error "invalid object name 'dbo.Items' (my table from access).
Please advice.
Thank you,
Yalon

ASKER CERTIFIED SOLUTION

CSLARSEN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ygnd

ASKER

Yes, with the change of db name.
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "DATA SOURCE=LepTop-2\SQLExPRESS;"
strConn = strConn & "Initial Catalog=WizPlan.V6;"
strConn = strConn & "Trusted_Connection=Yes;"
Dim DBcn As ADODB.Connection
Set DBcn = New ADODB.Connection
DBcn.Open strConn
sSQL = ""
sSQL = sSQL & "SELECT * FROM bdo.Items;"
Dim rst As ADODB.Recordset
Set rst = DBcn.Execute(sSQL)

ygnd

ASKER

I'm using dbo.Items

ygnd

ASKER

Done.
It ok.
I hade Miss write of the table name.
Thank you
Yalon

CSLARSEN

ok great,
so you manage to grab data from access with the above connection string?
cheers
cslarsen

ygnd

ASKER

I have an Excel application that using date from access which I'm now converting to sql server 2005 express.
With your help, it is already runing from the sql.
But I still have some more question comming ahead.
I exported tables from access to sql, using export method in the access through ODBC connection.
I found your service, very effective.
Thank you.
Yalon