Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Other Microsoft Products _ Creating An Access Database Using Visual Studio 2017

Posted by: PC_User Aug 25 2017, 07:02 PM

I'm trying to learn Visual Studio with visual basic as used in MS Access. I've been searching for instructions, tutorials or videos on how to create an Access database table using Visual Studio 2017. I'm choosing the Access database instead of SQL, because I've used MS Access for years and I might like to use Access to use the table someday. Most videos that I've come across use the C language or create an SQL table, so I'm asking this forum for help finding this information.


Posted by: DanielPineault Aug 25 2017, 07:58 PM

I'm still not clear as to what you want to do exactly.

Do you mean you want to create tables, forms, reports, ... all from VS instead of Access? It isn't possible. Why wouldn't you use Access directly for this. If not, please explain further.

Posted by: GroverParkGeorge Aug 25 2017, 08:47 PM

As Daniel already pointed out, this is not possible.

Visual Basic, as it refers to the coding language in the Visual Studio environment is not the same as Visual Basic for Applications, which is the coding language in Office applications, although they are very similar in many ways.

If you want to create and use a tables in an accdb file, that's a different thing. Is that what you are trying to do?

Posted by: PC_User Aug 26 2017, 04:21 PM

I was exploring this possibility after searching the internet and found these articles, but as you say the Visual Basic is not the same.

Posted by: GroverParkGeorge Aug 26 2017, 04:30 PM

Repeating the question....

Are you interested in using the ACE database engine in an accdb for data storage with an application created in Visual Studio?

Or, as might be inferred from your original question, did you want to try to create a complete Access database application using Visual Studio?

The former would be feasible, the latter not possible.

Let's be clear about one thing that often seems to confuse some people. It is all too common to refer to "Access database" when the intended reference is only to the tables, as they are implemented using the JET--now ACE--database engine.

Posted by: PC_User Aug 26 2017, 04:41 PM

I'm testing code that I might use as part of another project. I don't want to discuss the larger project as it will distract from my question. I may need to create additional tables for data storage. I'm using Visual Studio 2017 so that I can compile a stand alone program and not have to rely on MS Access to use the program. I've come across an old sample of how this can be done and I've upgraded it to Visual Studio 2017. This is the link to the project and will expire in 2 days. <<removed external link>>

See the relevant portion of the UA Guidelines:

"Uploads - (attaching) Images and files...please keep them compacted and small - all files uploaded to UtterAccess are scanned for viruses/trojans and the like before they're made available to download by other members.
NOTE 1: When posting images, upload them - DO NOT USE AN OFF-SITE FILE LINK of any type or hosting service like photobucket, imageshack, googledrive, onedrive or any other - they are not permitted. The only off-site linking permitted is YouTube and only when the YouTube video is pertinent to resolving a problem. If you are unable to attach your image or file for any reason, please ask for help - do not resort to using off-site storage - such links will be removed. The UtterAccess Administration Team has the final word.
NOTE 2: When attaching non-image files (MDB/ACCDB/etc), you MUST "zip" them first.
NOTE 3: When attaching a zipped Access database, PLEASE ensure the code compiles properly and do a Compact & Repair BEFORE zipping them."

Posted by: MadPiet Aug 26 2017, 08:47 PM

Before you go too deep into that, note that VBA in Access is much more closely related to VB6 than VisualBasic.NET.

You don't really need any other programming applications (like .NET) to program Access. If you want to build the UI in .NET, go for it... Then maybe upsize your database to SQL Server (downloading the Developer Edition is free), and tweak your stuff so it works with SQL Server...

Not sure about creating an entire Access DB with VS2017 though...

But if you can do both SQL Server and .NET, that's easily worth $60-80K a year...

Posted by: GroverParkGeorge Aug 26 2017, 09:32 PM

"I may need to create additional tables for data storage."

In that case, yes, you can create additional tables in an existing mdb or accdb. I would be quite surprised to learn you can create the mdb or accdb itself, but would never have considered doing so and never investigated it.

Posted by: GroverParkGeorge Aug 27 2017, 07:33 AM

Well, I found Try it out.

I'm pleased to learn something new today. It shouldn't have surprised me, once I thought about it. Thanks for pushing.

Posted by: PC_User Aug 27 2017, 10:29 AM

I have code from Excel 2017. Would it be compatible with Visual Studio 2017 after taking out all the references to cells and worksheets? Maybe I should be making my application if VB6?

Posted by: GroverParkGeorge Aug 27 2017, 10:34 AM

I have no idea how to address question that since a) I can't see your code and b) I don't know how you'd use it in VS.

Posted by: PC_User Aug 27 2017, 11:06 AM

This is the Excel code to download stock quotes from Google Finance and export them into a txt file. Are there compatibility problems with Visual Studio 2017.

Option Explicit

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias _"URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal _
szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As LongPtr) As Long

Sub GetData()
    Dim ParameterSheet As Worksheet
    Dim DataSheet As Worksheet
    Dim ticker As String
    Dim exchange As String
    Dim interval As Integer
    Dim numPastTradingDays As Integer
    Dim qurl As String
    Dim CSVstatus As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    Set ParameterSheet = Sheets("Parameters")
    Set DataSheet = Sheets("Data")

    ticker = ParameterSheet.Range("ticker").Value
    exchange = ParameterSheet.Range("exchange").Value
    interval = ParameterSheet.Range("interval").Value
    numPastTradingDays = ParameterSheet.Range("numTradingDays").Value

    qurl = "" & _
           "q=" & ticker & _
           "&i=" & interval & _
           "&p=" & numPastTradingDays & "d" & _

    CSVstatus = URLDownloadToFile(0, qurl, "C:\quotes.csv", 0, 0)

    With DataSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("a1"))
        .BackgroundQuery = True
        .TablesOnlyFromHTML = False
        .Refresh BackgroundQuery:=False
        .SaveData = True
    End With

    DataSheet.Range("a1").CurrentRegion.TextToColumns Destination:=DataSheet.Range("a1"), DataType:=xlDelimited, _
                                                      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                                      Semicolon:=False, Comma:=True, Space:=False, other:=False

    DataSheet.Columns("A:G").ColumnWidth = 12

    '===Convert Google timestamp to Excel timestamp (only for Windows)
    Dim timeStamp As Double
    Dim timeStampRaw As String
    Dim timeZoneOffsetRaw As String
    Dim timeZoneOffset As Variant
    Dim numRows As Integer
    Dim i As Integer
    numRows = DataSheet.UsedRange.Rows.Count - 1

    timeZoneOffsetRaw = DataSheet.Range("a7")
    timeZoneOffset = (Mid(timeZoneOffsetRaw, InStr(timeZoneOffsetRaw, "=") + 1, 10))

    For i = 8 To numRows

        If Not IsNumeric(DataSheet.Range("a" & i)) Then

            timeStampRaw = DataSheet.Range("a" & i)
            timeStamp = (Mid(timeStampRaw, 2, Len(timeStampRaw) - 1))
            timeStamp = (timeStamp + timeZoneOffset * 60)
            DataSheet.Range("g" & i) = timeStamp / 86400 + 25569


            DataSheet.Range("g" & i).FormulaR1C1 = "=(RC[-6]*" & interval & "+" & timeStamp & ")/86400+25569"

        End If


    DataSheet.Range("g8:g" & numRows).NumberFormat = "d mmm yyyy h:mm;@"

    Application.Calculation = xlCalculationAutomatic

End Sub

I've tried to adapt the Excel code to VS 2017 code shown here.

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias  
    _"URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal
    _ szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As LongPtr) As Long

    Sub GetData()
        Dim Ticker As String
        Dim Exchange As String
        Dim Interval As Integer
        Dim numPastTradingDays As Integer
        Dim Qurl As String

        Ticker = txtTicker.Text
        Exchange = txtExchange.Text
        Interval = cboInterval.Text
        numPastTradingDays = cboPastDays.Text

        Qurl = "" &
               "q=" & Ticker &
               "&i=" & Interval &
               "&p=" & numPastTradingDays & "d" &

        CSVstatus = URLDownloadToFile(0, Qurl, "C:\quotes.txt", 0, 0)

    End Sub

Posted by: ScottGem Aug 27 2017, 11:10 AM

You might want to check out my blog on What Access is. Many people misunderstand that Access is actually a group of components that work interactively. One of those components is the ACE which is the data store used by Access. However, one of the strengths of the Access platform is that some of the parts are interchangeable. One of those interchangeable parts is the data store. You can use ACE or you can use SQL Express, SQL Server, Oracle, MySQL, etc. Given that ACE is the weakest component of the Access platform, I'm not sure why one would choose to use it when building a stand alone executable with C or vb.Net or another language.

If you use SQL Express, for example, you can still use the data later if you want to develop an Access front end.

The code you show is VBA code. As noted earlier Visual Studio does not work with VBA.

Posted by: GroverParkGeorge Aug 27 2017, 11:13 AM

Not having tried to do this, I can't say, but this seems to be a different question on a different topic, right?

The only way to know if this code would work would be, I imagine, to try it. Have you tried it? If so, what happened?

Since you are trying to implement Excel VBA using VB.NET in VS, I suspect you'd be further ahead trying to figure out how VB.NET handles such tasks and start there.

Sorry, but what you are asking is sort of the equivalent of going into a Ford dealership and asking how to convert your F150 to use Chevy replacement parts. Maybe that's possible, but the right place to ask is the Chevrolet dealership, right?

I do see something that I very much doubt will transfer.

"Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias
_"URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal
_ szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As LongPtr) As Long"

That's the declaration for an API in the VBA world. I doubt it is the same in VB.NET....