I have a MySQL database running on Heroku and I’m trying to connect to it using VBA code in Excel. The MySQL for Excel add-in works fine for connections, but when I try to use VBA I keep getting an access denied error.
The error message says:
Access denied for user ‘user’ to database ‘database’
I think this might be related to user permissions or database configuration rather than my code itself. Here’s what I’m using:
Dim connection As ADODB.Connection
Set connection = New ADODB.Connection
Dim connectionString As String
connectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=myServer;DATABASE=myDB;USER=dbUser;PASSWORD=dbPass"
connection.Open connectionString
Any ideas what could be causing this access issue?
Check if ur Heroku MySQL instance allows remote connections - some plans block external access by default. Try adding port 3306 explicitly to ur connection string: PORT=3306;. I’d also test with a basic ODBC driver first b4 trying the Unicode one.
Had the same Heroku MySQL headaches with VBA. Connection timeout got me first - Heroku’s way stricter than local databases. Add CONNECTION_TIMEOUT=30; to your connection string. Also check your database user permissions. Mine worked fine in other clients but failed in VBA. Log into Heroku MySQL and run SHOW GRANTS FOR 'your_user'@'%'; - sometimes you can access tables but not the database schema itself. One more thing - cheaper Heroku plans limit concurrent connections. VBA might be trying to open multiple connections and hitting that wall, which throws weird access errors.
I’ve hit this same issue with Heroku databases and VBA. The problem’s usually how Heroku handles database URLs - they give you one DATABASE_URL string with everything bundled together, but VBA ADODB wants separate parameters. Grab the connection details straight from your Heroku config vars instead of guessing the format. Also check if your Heroku MySQL needs SSL - most do by default. Try adding SSLMODE=REQUIRED; to your connection string. Big gotcha: Heroku rotates database credentials automatically, so any hardcoded values in your VBA will eventually break. I’d test those exact credentials through a basic MySQL client first - that’ll tell you if it’s a VBA problem or just bad auth.