Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-14-2019, 11:50 AM   #1
lyuzhongkun
Newly Registered User
 
Join Date: Jan 2019
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
lyuzhongkun is on a distinguished road
Can you help me?

I want to wrote a vb to get data from SQL Server to Excel. But when I run this, I get an compile error msg "Expected variable or procedure, not module" and direct to the MsgBox line. I not sure why this happen.

Sub SQLDatabase()

Dim rs As ADODB.Recordset ' hold data
Dim cnSQL As ADODB.Connection '
Dim sqlString As String
Dim colOffset As Integer
Dim qf As Object

colOffset = 0

Sheets("Database").Select
Cells.ClearContents

Set cnSQL = New ADODB.Connection 'instantiates the connection.
cnSQL.Open "provider = SQLOLEDB.1;INtegrated Security =SSPI; Initial Catalog = sample1; Data source =LAPTOP-THDFQP9K\SQLEXPRESS"

sqlString = "select id, name, email from tbperson "

Set rs = New ADODB.Recordset
rs.Open sqlString, cnSQL, adOpenStatic, adLockOptimistic

If rs.EOF Then
MsgBox ("The recordset is empty, rs.eof =" & rs.EOF)
Else
MsgBox "# of record =" & rs.RecordCount

For Each qf In rs.Fields
Range("A1").Offset(0, colOffset).Value = qf.name
colOffset = colOffset + 1
Next qf

ActiveSheet.Cells(2, 1).CopyFromRecordset rs

End If

rs.Close
Set rs = Nothing


End Sub

lyuzhongkun is offline   Reply With Quote
Old 01-14-2019, 12:21 PM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,602
Thanks: 26
Thanked 487 Times in 463 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Can you help me?

Not sure if it is your only problem, but you use () when you return a value only from a function, exclude them when not.

msgbox "some message"
vs
rtn = Msgbox("Some Value",VByesNO)
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lyuzhongkun (01-14-2019)
Old 01-14-2019, 12:21 PM   #3
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,602
Thanks: 26
Thanked 487 Times in 463 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Can you help me?

Also make sure your module name is not the same as your procedure.

MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
lyuzhongkun (01-14-2019)
Old 01-14-2019, 12:34 PM   #4
lyuzhongkun
Newly Registered User
 
Join Date: Jan 2019
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
lyuzhongkun is on a distinguished road
Re: Can you help me?

Quote:
Originally Posted by MajP View Post
Not sure if it is your only problem, but you use () when you return a value only from a function, exclude them when not.

msgbox "some message"
vs
rtn = Msgbox("Some Value",VByesNO)
I assure the msgbox is the only error. I change the format to msgbox "some message" but still failed. And what I found is that When I use msgbox in other modules in the same workbook, they all failed and pump up same compile error. But when I change to other workbooks, the error message is gone. Is that possible I set up something wrong in this specific workbook?
lyuzhongkun is offline   Reply With Quote
Old 01-14-2019, 12:38 PM   #5
lyuzhongkun
Newly Registered User
 
Join Date: Jan 2019
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
lyuzhongkun is on a distinguished road
Thumbs up Re: Can you help me?

Quote:
Originally Posted by MajP View Post
Also make sure your module name is not the same as your procedure.
Thanks, I found my mistake. I just doesn't know why I add a module named msgbox in the worksheet. How stupid am I and Thanks for your help!

lyuzhongkun is offline   Reply With Quote
Reply

Tags
excel , sql server , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 03:44 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World