Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Connection macro problem (Read 7175 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Connection macro problem
15.09.10 at 10:42:08
 
Connection macro problem
 
Quote:
I am having a small problem perhaps you could advise. I can connect to db just fine from sql dialog in excel but when i record a macro and then try to connect through macro i get the following error. So manually it works fine but when automated through macro it gets this error:

Driver Error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (-2147467259)

 
This is strange as it should record what it used to connect.  
 
Can you select the connectio in the connect dialog and then open it in the editor? What you get displayed should be listed in the connectionstring section of the macro call. Please let me know if this is different.  
 
Quote:
Seems about the same:

Driver={SQL Server}; Server=MyPC\SQLEXPRESS; Database=dbname; Trusted_Connection=yes

the only thing missing is Driver={SQL Server}

Provider = MSDASQL
Database = MyPC\SQLEXPRESS\dbname

Can I add that ? If so what is the syntax?

 
What you extracted is the so called connection string. This is used to connect to your database and you will see that it is a parameter in the macro call.
 
What is strange is that the details you extracted are different from the recorded macro. That should be not possible because the form that 'remembers' your connection uses the same technique to save the connection details as the macro recorder does.
 
Anyway, it is different so to correct the macro replace what is now in the ConnectionString parameter to what you extracted:
 
OId:
    SQLXL.Database.Connect UserName:="me", Password:="pwd", DBAlias:="MyPC\SQLEXPRESS\dbname", ConnectionString:="Provider=MSDASQL.1;User ID=me;", AllowTransactions:=True
 
New:
    SQLXL.Database.Connect UserName:="me", Password:="pwd", DBAlias:="MyPC\SQLEXPRESS\dbname", ConnectionString:="Driver={SQL Server}; Server=MyPC\SQLEXPRESS; Database=dbname; Trusted_Connection=yes", AllowTransactions:=True
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Twildey
YaBB Newbies
*


I Love SQL*XL

Posts: 7
Re: Connection macro problem
Reply #1 - 07.07.11 at 18:25:59
 
I am having a problem connecting to Oracle via the sample macro you have posted.  I can connect fine when using the SQL dialogue box, but not with the macro.  Here is my code
 
Sub DBConnection()
 
    If SQLXL.Database.Connected = True Then
       SQLXL.Database.Disconnect
    End If
    SQLXL.Database.Connect "username", "password", "database name", litOO4O
    If SQLXL.Database.Connected = True Then
       MsgBox "Connected"
    Else
       MsgBox "Connection Failed"
    End If
End Sub
 
It gets hung up before it even start.  Really not sure what is going on.  Can you help
Back to top
 
« Last Edit: 30.07.14 at 13:48:30 by Gerrit-Jan Linker »  
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Connection macro problem
Reply #2 - 07.07.11 at 19:29:24
 
It is likely that the SQLXL reference was not added to your project. When you record a macro the reference is created automatically. Otherwise please manually add a reference in the VB projects (Press Alt-F11 to go to the VB projects) to the SQLXL object (sqlxl.xla).
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Twildey
YaBB Newbies
*


I Love SQL*XL

Posts: 7
Re: Connection macro problem
Reply #3 - 08.07.11 at 14:27:04
 
That was it!  Thanks very much.  I should have known better.
Back to top
 
 
  IP Logged
Suzan
YaBB Newbies
*


I Love SQL*XL

Posts: 1
Re: Connection macro problem
Reply #4 - 07.10.11 at 13:41:10
 
I was knowing that while recording macro the reference is created automatically.But I was not aware that manually a reference should be added in the VB projects to the SQLXL object .Thanks for providing such useful information.
Back to top
 
 
Email suzanmarvel suzanmarvel   IP Logged
Pages: 1