CODEWITHSUNDEEP
sql-servervbaexcel
yaz_ozzie
yaz_ozzie

Reputation: 53

SQL to Excel connection in VBA

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"}

error message

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.

Upvotes: 0

Views: 773

Answers (2)

yaz_ozzie
yaz_ozzie

Reputation: 53

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

Upvotes: 0

David W
David W

Reputation: 10184

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

Upvotes: 1

Related Questions