UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Creating An Access Database Using Visual Studio 2017, Visual Studio 2017    
 
   
PC_User
post Aug 25 2017, 07:02 PM
Post#1



Posts: 366
Joined: 24-April 03
From: Los Angeles, California USA


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.

Thanks,
PC

--------------------
Regards,
PC User
I'm using MS Access 2017 and Visual Studio 2017
Go to the top of the page
 
DanielPineault
post Aug 25 2017, 07:58 PM
Post#2


UtterAccess VIP
Posts: 5,453
Joined: 30-June 11



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.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
GroverParkGeorge
post Aug 25 2017, 08:47 PM
Post#3


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


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?

--------------------
Go to the top of the page
 
PC_User
post Aug 26 2017, 04:21 PM
Post#4



Posts: 366
Joined: 24-April 03
From: Los Angeles, California USA


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

http://www.codeguru.com/vb/gen/vb_database...l-Basic-NET.htm

https://social.msdn.microsoft.com/Forums/en...forum=vbinterop

http://www.freevbcode.com/ShowCode.asp?ID=5797

--------------------
Regards,
PC User
I'm using MS Access 2017 and Visual Studio 2017
Go to the top of the page
 
GroverParkGeorge
post Aug 26 2017, 04:30 PM
Post#5


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
Go to the top of the page
 
PC_User
post Aug 26 2017, 04:41 PM
Post#6



Posts: 366
Joined: 24-April 03
From: Los Angeles, California USA


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

--------------------
Regards,
PC User
I'm using MS Access 2017 and Visual Studio 2017
Go to the top of the page
 
MadPiet
post Aug 26 2017, 08:47 PM
Post#7



Posts: 2,264
Joined: 27-February 09



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...
Go to the top of the page
 
GroverParkGeorge
post Aug 26 2017, 09:32 PM
Post#8


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
Go to the top of the page
 
GroverParkGeorge
post Aug 27 2017, 07:33 AM
Post#9


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


Well, I found this reference. 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.

--------------------
Go to the top of the page
 
PC_User
post Aug 27 2017, 10:29 AM
Post#10



Posts: 366
Joined: 24-April 03
From: Los Angeles, California USA


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?

--------------------
Regards,
PC User
I'm using MS Access 2017 and Visual Studio 2017
Go to the top of the page
 
GroverParkGeorge
post Aug 27 2017, 10:34 AM
Post#11


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
Go to the top of the page
 
PC_User
post Aug 27 2017, 11:06 AM
Post#12



Posts: 366
Joined: 24-April 03
From: Los Angeles, California USA


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.

CODE
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")

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

    qurl = "http://www.google.com/finance/getprices?" & _
           "q=" & ticker & _
           "&i=" & interval & _
           "&p=" & numPastTradingDays & "d" & _
           "&f=d,o,h,l,c,v"

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

QueryQuote:
    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

        Else

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

        End If

    Next

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

    Application.Calculation = xlCalculationAutomatic

End Sub


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

CODE
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 = "http://www.google.com/finance/getprices?" &
               "q=" & Ticker &
               "&i=" & Interval &
               "&p=" & numPastTradingDays & "d" &
               "&f=d,o,h,l,c,v"

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

    End Sub

--------------------
Regards,
PC User
I'm using MS Access 2017 and Visual Studio 2017
Go to the top of the page
 
ScottGem
post Aug 27 2017, 11:10 AM
Post#13


UtterAccess VIP / UA Clown
Posts: 32,183
Joined: 21-January 04
From: LI, NY


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.

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

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
GroverParkGeorge
post Aug 27 2017, 11:13 AM
Post#14


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


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....

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:16 PM