SQL to Excel connection in VBA

Question

I have created a connection between Excel and SQL Server using a VBA macro but am having some difficulty with the connection string - specifically the Initial Catalog parameter.

When I run the following, my connection is fine and I can copy data into Excel by referring to the full table path ([dbname].[dbo].[tablename]).

{conn.open = "Provider=SQLOLEDB;Data Source=" & Server_Name & ";Trusted_connection=yes"}

However, when I run a different version of the connection string (below), the connection fails with the following error:

{"Provider=SQLOLEDB;Data Source=" & Server_Name & ";Initial Catalog=" & Database_Name & ";Trusted_connection=yes"}

The error therefore appears to be in the initialising of the target database - I am not an admin user, but don't understand why the login would fail since I have access to the database and can connect to the server fine. Any help would be appreciated.

What is the Database_Name? It says it cannot open it. Is it real? Does it have restrictions? — Vityata
– Vityata, Commented Feb 21, 2017 at 15:37

David W · Accepted Answer · 2017-02-21 15:42:55Z

1

In the second example, rather than "Trusted_Connection=Yes", try "Integrated Security=SSPI"

answered Feb 21, 2017 at 15:42

David W

10.2k38 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

yaz_ozzie · Accepted Answer · 2017-02-22 11:47:04Z

0

Thanks for the replies - I have now managed to figure out the issue. The database was fine in that it existed and had no restrictions with regards my access. In addition, the access to the server was fine (e.g. in terms of user credentials or SSPI vs trusted connection).

The issue was actually just a really basic error - the database_name variable was stored in square brackets (as it would be referred to in the normal SQL code):

{database_name = "[database]"}

Removing the square brackets enabled the code work

answered Feb 22, 2017 at 11:47

yaz_ozzie

531 silver badge6 bronze badges

Collectives™ on Stack Overflow

SQL to Excel connection in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related